###############################################################################
#
# Worksheet - A class for writing the Excel XLSX Worksheet file.
#
# Copyright 2013-2018, John McNamara, jmcnamara@cpan.org
#

# Standard packages.
import codecs
import datetime
import os
import re
import sys
import tempfile

from warnings import warn

# Standard packages in Python 2/3 compatibility mode.
from .compatibility import StringIO
from .compatibility import defaultdict
from .compatibility import namedtuple
from .compatibility import force_unicode
from .compatibility import num_types, str_types

# Package imports.
from . import xmlwriter
from .format import Format
from .drawing import Drawing
from .shape import Shape
from .xmlwriter import XMLwriter
from .utility import xl_rowcol_to_cell
from .utility import xl_rowcol_to_cell_fast
from .utility import xl_cell_to_rowcol
from .utility import xl_col_to_name
from .utility import xl_range
from .utility import xl_color
from .utility import get_sparkline_style
from .utility import supported_datetime
from .utility import datetime_to_excel_datetime
from .utility import quote_sheetname
from .exceptions import DuplicateTableName


###############################################################################
#
# Decorator functions.
#
###############################################################################
def convert_cell_args(method):
    """
    Decorator function to convert A1 notation in cell method calls
    to the default row/col notation.

    """
    def cell_wrapper(self, *args, **kwargs):

        try:
            # First arg is an int, default to row/col notation.
            if len(args):
                first_arg = args[0]
                int(first_arg)
        except ValueError:
            # First arg isn't an int, convert to A1 notation.
            new_args = xl_cell_to_rowcol(first_arg)
            args = new_args + args[1:]

        return method(self, *args, **kwargs)

    return cell_wrapper


def convert_range_args(method):
    """
    Decorator function to convert A1 notation in range method calls
    to the default row/col notation.

    """
    def cell_wrapper(self, *args, **kwargs):

        try:
            # First arg is an int, default to row/col notation.
            if len(args):
                int(args[0])
        except ValueError:
            # First arg isn't an int, convert to A1 notation.
            if ':' in args[0]:
                cell_1, cell_2 = args[0].split(':')
                row_1, col_1 = xl_cell_to_rowcol(cell_1)
                row_2, col_2 = xl_cell_to_rowcol(cell_2)
            else:
                row_1, col_1 = xl_cell_to_rowcol(args[0])
                row_2, col_2 = row_1, col_1

            new_args = [row_1, col_1, row_2, col_2]
            new_args.extend(args[1:])
            args = new_args

        return method(self, *args, **kwargs)

    return cell_wrapper


def convert_column_args(method):
    """
    Decorator function to convert A1 notation in columns method calls
    to the default row/col notation.

    """
    def column_wrapper(self, *args, **kwargs):

        try:
            # First arg is an int, default to row/col notation.
            if len(args):
                int(args[0])
        except ValueError:
            # First arg isn't an int, convert to A1 notation.
            cell_1, cell_2 = [col + '1' for col in args[0].split(':')]
            _, col_1 = xl_cell_to_rowcol(cell_1)
            _, col_2 = xl_cell_to_rowcol(cell_2)
            new_args = [col_1, col_2]
            new_args.extend(args[1:])
            args = new_args

        return method(self, *args, **kwargs)

    return column_wrapper


###############################################################################
#
# Named tuples used for cell types.
#
###############################################################################
cell_string_tuple = namedtuple('String', 'string, format')
cell_number_tuple = namedtuple('Number', 'number, format')
cell_blank_tuple = namedtuple('Blank', 'format')
cell_boolean_tuple = namedtuple('Boolean', 'boolean, format')
cell_formula_tuple = namedtuple('Formula', 'formula, format, value')
cell_arformula_tuple = namedtuple('ArrayFormula',
                                  'formula, format, value, range')


###############################################################################
#
# Worksheet Class definition.
#
###############################################################################
class Worksheet(xmlwriter.XMLwriter):
    """
    A class for writing the Excel XLSX Worksheet file.

    """

    ###########################################################################
    #
    # Public API.
    #
    ###########################################################################

    def __init__(self):
        """
        Constructor.

        """

        super(Worksheet, self).__init__()

        self.name = None
        self.index = None
        self.str_table = None
        self.palette = None
        self.constant_memory = 0
        self.tmpdir = None
        self.is_chartsheet = False

        self.ext_sheets = []
        self.fileclosed = 0
        self.excel_version = 2007
        self.excel2003_style = False

        self.xls_rowmax = 1048576
        self.xls_colmax = 16384
        self.xls_strmax = 32767
        self.dim_rowmin = None
        self.dim_rowmax = None
        self.dim_colmin = None
        self.dim_colmax = None

        self.colinfo = {}
        self.selections = []
        self.hidden = 0
        self.active = 0
        self.tab_color = 0

        self.panes = []
        self.active_pane = 3
        self.selected = 0

        self.page_setup_changed = False
        self.paper_size = 0
        self.orientation = 1

        self.print_options_changed = False
        self.hcenter = False
        self.vcenter = False
        self.print_gridlines = False
        self.screen_gridlines = True
        self.print_headers = False
        self.row_col_headers = False

        self.header_footer_changed = False
        self.header = ''
        self.footer = ''
        self.header_footer_aligns = True
        self.header_footer_scales = True
        self.header_images = []
        self.footer_images = []
        self.header_images_list = []

        self.margin_left = 0.7
        self.margin_right = 0.7
        self.margin_top = 0.75
        self.margin_bottom = 0.75
        self.margin_header = 0.3
        self.margin_footer = 0.3

        self.repeat_row_range = ''
        self.repeat_col_range = ''
        self.print_area_range = ''

        self.page_order = 0
        self.black_white = 0
        self.draft_quality = 0
        self.print_comments = 0
        self.page_start = 0

        self.fit_page = 0
        self.fit_width = 0
        self.fit_height = 0

        self.hbreaks = []
        self.vbreaks = []

        self.protect_options = {}
        self.set_cols = {}
        self.set_rows = defaultdict(dict)

        self.zoom = 100
        self.zoom_scale_normal = 1
        self.print_scale = 100
        self.is_right_to_left = 0
        self.show_zeros = 1
        self.leading_zeros = 0

        self.outline_row_level = 0
        self.outline_col_level = 0
        self.outline_style = 0
        self.outline_below = 1
        self.outline_right = 1
        self.outline_on = 1
        self.outline_changed = False

        self.original_row_height = 15
        self.default_row_height = 15
        self.default_row_pixels = 20
        self.default_col_pixels = 64
        self.default_row_zeroed = 0

        self.names = {}
        self.write_match = []
        self.table = defaultdict(dict)
        self.merge = []
        self.row_spans = {}

        self.has_vml = False
        self.has_header_vml = False
        self.has_comments = False
        self.comments = defaultdict(dict)
        self.comments_list = []
        self.comments_author = ''
        self.comments_visible = 0
        self.vml_shape_id = 1024
        self.buttons_list = []
        self.vml_header_id = 0

        self.autofilter_area = ''
        self.autofilter_ref = None
        self.filter_range = []
        self.filter_on = 0
        self.filter_cols = {}
        self.filter_type = {}

        self.col_sizes = {}
        self.row_sizes = {}
        self.col_formats = {}
        self.col_size_changed = False
        self.row_size_changed = False

        self.last_shape_id = 1
        self.rel_count = 0
        self.hlink_count = 0
        self.hlink_refs = []
        self.external_hyper_links = []
        self.external_drawing_links = []
        self.external_comment_links = []
        self.external_vml_links = []
        self.external_table_links = []
        self.drawing_links = []
        self.vml_drawing_links = []
        self.charts = []
        self.images = []
        self.tables = []
        self.sparklines = []
        self.shapes = []
        self.shape_hash = {}
        self.drawing = 0

        self.rstring = ''
        self.previous_row = 0

        self.validations = []
        self.cond_formats = {}
        self.data_bars_2010 = []
        self.use_data_bars_2010 = False
        self.dxf_priority = 1
        self.page_view = 0

        self.vba_codename = None

        self.date_1904 = False
        self.hyperlinks = defaultdict(dict)

        self.strings_to_numbers = False
        self.strings_to_urls = True
        self.nan_inf_to_errors = False
        self.strings_to_formulas = True

        self.default_date_format = None
        self.default_url_format = None
        self.remove_timezone = False

        self.row_data_filename = None
        self.row_data_fh = None
        self.worksheet_meta = None
        self.vml_data_id = None
        self.vml_shape_id = None

        self.row_data_filename = None
        self.row_data_fh = None
        self.row_data_fh_closed = False

        self.vertical_dpi = 0
        self.horizontal_dpi = 0

    # Utility function for writing different types of strings.
    def _write_token_as_string(self, token, row, col, *args):
        # Map the data to the appropriate write_*() method.
        if token is '':
            return self._write_blank(row, col, *args)

        if self.strings_to_formulas and token.startswith('='):
            return self._write_formula(row, col, *args)

        if token.startswith('{=') and token.endswith('}'):
            return self._write_formula(row, col, *args)

        if ':' in token:
            if self.strings_to_urls and re.match('(ftp|http)s?://', token):
                return self._write_url(row, col, *args)
            elif self.strings_to_urls and re.match('mailto:', token):
                return self._write_url(row, col, *args)
            elif self.strings_to_urls and re.match('(in|ex)ternal:', token):
                return self._write_url(row, col, *args)

        if self.strings_to_numbers:
            try:
                f = float(token)
                if (self.nan_inf_to_errors or
                        (not self._isnan(f) and not self._isinf(f))):
                    return self._write_number(row, col, f, *args[1:])
            except ValueError:
                # Not a number, write as a string.
                pass

            return self._write_string(row, col, *args)

        else:
            # We have a plain string.
            return self._write_string(row, col, *args)

    @convert_cell_args
    def write(self, row, col, *args):
        """
        Write data to a worksheet cell by calling the appropriate write_*()
        method based on the type of data being passed.

        Args:
            row:   The cell row (zero indexed).
            col:   The cell column (zero indexed).
            *args: Args to pass to sub functions.

        Returns:
             0:    Success.
            -1:    Row or column is out of worksheet bounds.
            other: Return value of called method.

        """
        return self._write(row, col, *args)

    # Undecorated version of write().
    def _write(self, row, col, *args):
        # Check the number of args passed.
        if not len(args):
            raise TypeError("write() takes at least 4 arguments (3 given)")

        # The first arg should be the token for all write calls.
        token = args[0]

        # Write None as a blank cell.
        if token is None:
            return self._write_blank(row, col, *args)

        # Avoid isinstance() for better performance.
        token_type = type(token)

        if token_type is bool:
            return self._write_boolean(row, col, *args)

        if token_type in num_types:
            return self._write_number(row, col, *args)

        if token_type is str:
            return self._write_token_as_string(token, row, col, *args)

        if token_type in (datetime.datetime,
                          datetime.date,
                          datetime.time,
                          datetime.timedelta):
            return self._write_datetime(row, col, *args)

        if sys.version_info < (3, 0, 0):
            if token_type is unicode:
                try:
                    return self._write_token_as_string(str(token),
                                                       row, col, *args)
                except (UnicodeEncodeError, NameError):
                    pass

        # Resort to isinstance() for subclassed primitives.

        # Write number types.
        if isinstance(token, num_types):
            return self._write_number(row, col, *args)

        # Write string types.
        if isinstance(token, str_types):
            return self._write_token_as_string(token, row, col, *args)

        # Write boolean types.
        if isinstance(token, bool):
            return self._write_boolean(row, col, *args)

        # Write datetime objects.
        if supported_datetime(token):
            return self._write_datetime(row, col, *args)

        # We haven't matched a supported type. Try float.
        try:
            f = float(token)
            return self._write_number(row, col, f, *args[1:])
        except ValueError:
            pass
        except TypeError:
            raise TypeError("Unsupported type %s in write()" % type(token))

        # Finally try string.
        try:
            str(token)
            return self._write_string(row, col, *args)
        except ValueError:
            raise TypeError("Unsupported type %s in write()" % type(token))

    @convert_cell_args
    def write_string(self, row, col, string, cell_format=None):
        """
        Write a string to a worksheet cell.

        Args:
            row:    The cell row (zero indexed).
            col:    The cell column (zero indexed).
            string: Cell data. Str.
            format: An optional cell Format object.

        Returns:
            0:  Success.
            -1: Row or column is out of worksheet bounds.
            -2: String truncated to 32k characters.

        """
        return self._write_string(row, col, string, cell_format)

    # Undecorated version of write_string().
    def _write_string(self, row, col, string, cell_format=None):

        str_error = 0

        # Check that row and col are valid and store max and min values.
        if self._check_dimensions(row, col):
            return -1

        # Check that the string is < 32767 chars.
        if len(string) > self.xls_strmax:
            string = string[:self.xls_strmax]
            str_error = -2

        # Write a shared string or an in-line string in constant_memory mode.
        if not self.constant_memory:
            string_index = self.str_table._get_shared_string_index(string)
        else:
            string_index = string

        # Write previous row if in in-line string constant_memory mode.
        if self.constant_memory and row > self.previous_row:
            self._write_single_row(row)

        # Store the cell data in the worksheet data table.
        self.table[row][col] = cell_string_tuple(string_index, cell_format)

        return str_error

    @convert_cell_args
    def write_number(self, row, col, number, cell_format=None):
        """
        Write a number to a worksheet cell.

        Args:
            row:         The cell row (zero indexed).
            col:         The cell column (zero indexed).
            number:      Cell data. Int or float.
            cell_format: An optional cell Format object.

        Returns:
            0:  Success.
            -1: Row or column is out of worksheet bounds.

        """
        return self._write_number(row, col, number, cell_format)

    # Undecorated version of write_number().
    def _write_number(self, row, col, number, cell_format=None):

        if self._isnan(number) or self._isinf(number):
            if self.nan_inf_to_errors:
                if self._isnan(number):
                    return self._write_formula(row, col, '#NUM!', cell_format,
                                               '#NUM!')
                elif self._isinf(number):
                    return self._write_formula(row, col, '1/0', cell_format,
                                               '#DIV/0!')
            else:
                raise TypeError(
                    "NAN/INF not supported in write_number() "
                    "without 'nan_inf_to_errors' Workbook() option")

        # Check that row and col are valid and store max and min values.
        if self._check_dimensions(row, col):
            return -1

        # Write previous row if in in-line string constant_memory mode.
        if self.constant_memory and row > self.previous_row:
            self._write_single_row(row)

        # Store the cell data in the worksheet data table.
        self.table[row][col] = cell_number_tuple(number, cell_format)

        return 0

    @convert_cell_args
    def write_blank(self, row, col, blank, cell_format=None):
        """
        Write a blank cell with formatting to a worksheet cell. The blank
        token is ignored and the format only is written to the cell.

        Args:
            row:         The cell row (zero indexed).
            col:         The cell column (zero indexed).
            blank:       Any value. It is ignored.
            cell_format: An optional cell Format object.

        Returns:
            0:  Success.
            -1: Row or column is out of worksheet bounds.

        """
        return self._write_blank(row, col, blank, cell_format)

    # Undecorated version of write_blank().
    def _write_blank(self, row, col, blank, cell_format=None):
        # Don't write a blank cell unless it has a format.
        if cell_format is None:
            return 0

        # Check that row and col are valid and store max and min values.
        if self._check_dimensions(row, col):
            return -1

        # Write previous row if in in-line string constant_memory mode.
        if self.constant_memory and row > self.previous_row:
            self._write_single_row(row)

        # Store the cell data in the worksheet data table.
        self.table[row][col] = cell_blank_tuple(cell_format)

        return 0

    @convert_cell_args
    def write_formula(self, row, col, formula, cell_format=None, value=0):
        """
        Write a formula to a worksheet cell.

        Args:
            row:         The cell row (zero indexed).
            col:         The cell column (zero indexed).
            formula:     Cell formula.
            cell_format: An optional cell Format object.
            value:       An optional value for the formula. Default is 0.

        Returns:
            0:  Success.
            -1: Row or column is out of worksheet bounds.

        """
        # Check that row and col are valid and store max and min values.
        return self._write_formula(row, col, formula, cell_format, value)

    # Undecorated version of write_formula().
    def _write_formula(self, row, col, formula, cell_format=None, value=0):
        if self._check_dimensions(row, col):
            return -1

        # Hand off array formulas.
        if formula.startswith('{') and formula.endswith('}'):
            return self._write_array_formula(row, col, row, col, formula,
                                             cell_format, value)

        # Remove the formula '=' sign if it exists.
        if formula.startswith('='):
            formula = formula.lstrip('=')

        # Write previous row if in in-line string constant_memory mode.
        if self.constant_memory and row > self.previous_row:
            self._write_single_row(row)

        # Store the cell data in the worksheet data table.
        self.table[row][col] = cell_formula_tuple(formula, cell_format, value)

        return 0

    @convert_range_args
    def write_array_formula(self, first_row, first_col, last_row, last_col,
                            formula, cell_format=None, value=0):
        """
        Write a formula to a worksheet cell.

        Args:
            first_row:    The first row of the cell range. (zero indexed).
            first_col:    The first column of the cell range.
            last_row:     The last row of the cell range. (zero indexed).
            last_col:     The last column of the cell range.
            formula:      Cell formula.
            cell_format:  An optional cell Format object.
            value:        An optional value for the formula. Default is 0.

        Returns:
            0:  Success.
            -1: Row or column is out of worksheet bounds.

        """
        return self._write_array_formula(first_row, first_col, last_row,
                                         last_col, formula, cell_format, value)

    # Undecorated version of write_array_formula().
    def _write_array_formula(self, first_row, first_col, last_row, last_col,
                             formula, cell_format=None, value=0):

        # Swap last row/col with first row/col as necessary.
        if first_row > last_row:
            first_row, last_row = last_row, first_row
        if first_col > last_col:
            first_col, last_col = last_col, first_col

        # Check that row and col are valid and store max and min values
        if self._check_dimensions(last_row, last_col):
            return -1

        # Define array range
        if first_row == last_row and first_col == last_col:
            cell_range = xl_rowcol_to_cell(first_row, first_col)
        else:
            cell_range = (xl_rowcol_to_cell(first_row, first_col) + ':'
                          + xl_rowcol_to_cell(last_row, last_col))

        # Remove array formula braces and the leading =.
        if formula[0] == '{':
            formula = formula[1:]
        if formula[0] == '=':
            formula = formula[1:]
        if formula[-1] == '}':
            formula = formula[:-1]

        # Write previous row if in in-line string constant_memory mode.
        if self.constant_memory and first_row > self.previous_row:
            self._write_single_row(first_row)

        # Store the cell data in the worksheet data table.
        self.table[first_row][first_col] = cell_arformula_tuple(formula,
                                                                cell_format,
                                                                value,
                                                                cell_range)

        # Pad out the rest of the area with formatted zeroes.
        if not self.constant_memory:
            for row in range(first_row, last_row + 1):
                for col in range(first_col, last_col + 1):
                    if row != first_row or col != first_col:
                        self._write_number(row, col, 0, cell_format)

        return 0

    @convert_cell_args
    def write_datetime(self, row, col, date, cell_format=None):
        """
        Write a date or time to a worksheet cell.

        Args:
            row:         The cell row (zero indexed).
            col:         The cell column (zero indexed).
            date:        Date and/or time as a datetime object.
            cell_format: A cell Format object.

        Returns:
            0:  Success.
            -1: Row or column is out of worksheet bounds.

        """
        return self._write_datetime(row, col, date, cell_format)

    # Undecorated version of write_datetime().
    def _write_datetime(self, row, col, date, cell_format=None):

        # Check that row and col are valid and store max and min values.
        if self._check_dimensions(row, col):
            return -1

        # Write previous row if in in-line string constant_memory mode.
        if self.constant_memory and row > self.previous_row:
            self._write_single_row(row)

        # Convert datetime to an Excel date.
        number = self._convert_date_time(date)

        # Add the default date format.
        if cell_format is None:
            cell_format = self.default_date_format

        # Store the cell data in the worksheet data table.
        self.table[row][col] = cell_number_tuple(number, cell_format)

        return 0

    @convert_cell_args
    def write_boolean(self, row, col, boolean, cell_format=None):
        """
        Write a boolean value to a worksheet cell.

        Args:
            row:         The cell row (zero indexed).
            col:         The cell column (zero indexed).
            boolean:     Cell data. bool type.
            cell_format: An optional cell Format object.

        Returns:
            0:  Success.
            -1: Row or column is out of worksheet bounds.

        """
        return self._write_boolean(row, col, boolean, cell_format)

    # Undecorated version of write_boolean().
    def _write_boolean(self, row, col, boolean, cell_format=None):

        # Check that row and col are valid and store max and min values.
        if self._check_dimensions(row, col):
            return -1

        # Write previous row if in in-line string constant_memory mode.
        if self.constant_memory and row > self.previous_row:
            self._write_single_row(row)

        if boolean:
            value = 1
        else:
            value = 0

        # Store the cell data in the worksheet data table.
        self.table[row][col] = cell_boolean_tuple(value, cell_format)

        return 0

    # Write a hyperlink. This is comprised of two elements: the displayed
    # string and the non-displayed link. The displayed string is the same as
    # the link unless an alternative string is specified. The display string
    # is written using the write_string() method. Therefore the max characters
    # string limit applies.
    #
    # The hyperlink can be to a http, ftp, mail, internal sheet, or external
    # directory urls.
    @convert_cell_args
    def write_url(self, row, col, url, cell_format=None,
                  string=None, tip=None):
        """
        Write a hyperlink to a worksheet cell.

        Args:
            row:    The cell row (zero indexed).
            col:    The cell column (zero indexed).
            url:    Hyperlink url.
            format: An optional cell Format object.
            string: An optional display string for the hyperlink.
            tip:    An optional tooltip.
        Returns:
            0:  Success.
            -1: Row or column is out of worksheet bounds.
            -2: String longer than 32767 characters.
            -3: URL longer than Excel limit of 255 characters.
            -4: Exceeds Excel limit of 65,530 urls per worksheet.
        """
        return self._write_url(row, col, url, cell_format, string, tip)

    # Undecorated version of write_url().
    def _write_url(self, row, col, url, cell_format=None,
                   string=None, tip=None):

        # Check that row and col are valid and store max and min values
        if self._check_dimensions(row, col):
            return -1

        # Set the displayed string to the URL unless defined by the user.
        if string is None:
            string = url

        # Default to external link type such as 'http://' or 'external:'.
        link_type = 1

        # Remove the URI scheme from internal links.
        if url.startswith('internal:'):
            url = url.replace('internal:', '')
            string = string.replace('internal:', '')
            link_type = 2

        # Remove the URI scheme from external links and change the directory
        # separator from Unix to Dos.
        external = False
        if url.startswith('external:'):
            url = url.replace('external:', '')
            url = url.replace('/', '\\')
            string = string.replace('external:', '')
            string = string.replace('/', '\\')
            external = True

        # Strip the mailto header.
        string = string.replace('mailto:', '')

        # Check that the string is < 32767 chars
        str_error = 0
        if len(string) > self.xls_strmax:
            warn("Ignoring URL since it exceeds Excel's string limit of "
                 "32767 characters")
            return -2

        # Copy string for use in hyperlink elements.
        url_str = string

        # External links to URLs and to other Excel workbooks have slightly
        # different characteristics that we have to account for.
        if link_type == 1:

            # Split url into the link and optional anchor/location.
            if '#' in url:
                url, url_str = url.split('#', 1)
            else:
                url_str = None

            url = self._escape_url(url)

            if url_str is not None and not external:
                url_str = self._escape_url(url_str)

            # Add the file:/// URI to the url for Windows style "C:/" link and
            # Network shares.
            if re.match(r'\w:', url) or re.match(r'\\', url):
                url = 'file:///' + url

            # Convert a .\dir\file.xlsx link to dir\file.xlsx.
            url = re.sub(r'^\.\\', '', url)

        # Excel limits the escaped URL and location/anchor to 255 characters.
        tmp_url_str = url_str or ''
        if len(url) > 255 or len(tmp_url_str) > 255:
            warn("Ignoring URL '%s' with link or location/anchor > 255 "
                 "characters since it exceeds Excel's limit for URLS" %
                 force_unicode(url))
            return -3

        # Check the limit of URLS per worksheet.
        self.hlink_count += 1

        if self.hlink_count > 65530:
            warn("Ignoring URL '%s' since it exceeds Excel's limit of "
                 "65,530 URLS per worksheet." % force_unicode(url))
            return -4

        # Write previous row if in in-line string constant_memory mode.
        if self.constant_memory and row > self.previous_row:
            self._write_single_row(row)

        # Add the default URL format.
        if cell_format is None:
            cell_format = self.default_url_format

        # Write the hyperlink string.
        self._write_string(row, col, string, cell_format)

        # Store the hyperlink data in a separate structure.
        self.hyperlinks[row][col] = {
            'link_type': link_type,
            'url': url,
            'str': url_str,
            'tip': tip}

        return str_error

    @convert_cell_args
    def write_rich_string(self, row, col, *args):
        """
        Write a "rich" string with multiple formats to a worksheet cell.

        Args:
            row:          The cell row (zero indexed).
            col:          The cell column (zero indexed).
            string_parts: String and format pairs.
            cell_format:  Optional Format object.

        Returns:
            0:  Success.
            -1: Row or column is out of worksheet bounds.
            -2: String truncated to 32k characters.
            -3: 2 consecutive formats used.
            -4: Empty string used.
            -5: Insufficient parameters.

        """

        return self._write_rich_string(row, col, *args)

    # Undecorated version of write_rich_string().
    def _write_rich_string(self, row, col, *args):

        tokens = list(args)
        cell_format = None
        str_length = 0
        string_index = 0

        # Check that row and col are valid and store max and min values
        if self._check_dimensions(row, col):
            return -1

        # If the last arg is a format we use it as the cell format.
        if isinstance(tokens[-1], Format):
            cell_format = tokens.pop()

        # Create a temp XMLWriter object and use it to write the rich string
        # XML to a string.
        fh = StringIO()
        self.rstring = XMLwriter()
        self.rstring._set_filehandle(fh)

        # Create a temp format with the default font for unformatted fragments.
        default = Format()

        # Convert list of format, string tokens to pairs of (format, string)
        # except for the first string fragment which doesn't require a default
        # formatting run. Use the default for strings without a leading format.
        fragments = []
        previous = 'format'
        pos = 0

        if len(tokens) <= 2:
            warn("You must specify more then 2 format/fragments for rich "
                 "strings. Ignoring input in write_rich_string().")
            return -5

        for token in tokens:
            if not isinstance(token, Format):
                # Token is a string.
                if previous != 'format':
                    # If previous token wasn't a format add one before string.
                    fragments.append(default)
                    fragments.append(token)
                else:
                    # If previous token was a format just add the string.
                    fragments.append(token)

                if token == '':
                    warn("Excel doesn't allow empty strings in rich strings. "
                         "Ignoring input in write_rich_string().")
                    return -4

                # Keep track of actual string str_length.
                str_length += len(token)
                previous = 'string'
            else:
                # Can't allow 2 formats in a row.
                if previous == 'format' and pos > 0:
                    warn("Excel doesn't allow 2 consecutive formats in rich "
                         "strings. Ignoring input in write_rich_string().")
                    return -3

                # Token is a format object. Add it to the fragment list.
                fragments.append(token)
                previous = 'format'

            pos += 1

        # If the first token is a string start the <r> element.
        if not isinstance(fragments[0], Format):
            self.rstring._xml_start_tag('r')

        # Write the XML elements for the $format $string fragments.
        for token in fragments:
            if isinstance(token, Format):
                # Write the font run.
                self.rstring._xml_start_tag('r')
                self._write_font(token)
            else:
                # Write the string fragment part, with whitespace handling.
                attributes = []

                if re.search(r'^\s', token) or re.search(r'\s$', token):
                    attributes.append(('xml:space', 'preserve'))

                self.rstring._xml_data_element('t', token, attributes)
                self.rstring._xml_end_tag('r')

        # Read the in-memory string.
        string = self.rstring.fh.getvalue()

        # Check that the string is < 32767 chars.
        if str_length > self.xls_strmax:
            return -2

        # Write a shared string or an in-line string in constant_memory mode.
        if not self.constant_memory:
            string_index = self.str_table._get_shared_string_index(string)
        else:
            string_index = string

        # Write previous row if in in-line string constant_memory mode.
        if self.constant_memory and row > self.previous_row:
            self._write_single_row(row)

        # Store the cell data in the worksheet data table.
        self.table[row][col] = cell_string_tuple(string_index, cell_format)

        return 0

    @convert_cell_args
    def write_row(self, row, col, data, cell_format=None):
        """
        Write a row of data starting from (row, col).

        Args:
            row:    The cell row (zero indexed).
            col:    The cell column (zero indexed).
            data:   A list of tokens to be written with write().
            format: An optional cell Format object.
        Returns:
            0:  Success.
            other: Return value of write() method.

        """
        for token in data:
            error = self._write(row, col, token, cell_format)
            if error:
                return error
            col += 1

        return 0

    @convert_cell_args
    def write_column(self, row, col, data, cell_format=None):
        """
        Write a column of data starting from (row, col).

        Args:
            row:    The cell row (zero indexed).
            col:    The cell column (zero indexed).
            data:   A list of tokens to be written with write().
            format: An optional cell Format object.
        Returns:
            0:  Success.
            other: Return value of write() method.

        """
        for token in data:
            error = self._write(row, col, token, cell_format)
            if error:
                return error
            row += 1

        return 0

    @convert_cell_args
    def insert_image(self, row, col, filename, options=None):
        """
        Insert an image with its top-left corner in a worksheet cell.

        Args:
            row:      The cell row (zero indexed).
            col:      The cell column (zero indexed).
            filename: Path and filename for image in PNG, JPG or BMP format.
            options:  Position, scale, url and data stream of the image.

        Returns:
            0:  Success.
            -1: Row or column is out of worksheet bounds.

        """
        # Check insert (row, col) without storing.
        if self._check_dimensions(row, col, True, True):
            warn('Cannot insert image at (%d, %d).' % (row, col))
            return -1

        if options is None:
            options = {}

        x_offset = options.get('x_offset', 0)
        y_offset = options.get('y_offset', 0)
        x_scale = options.get('x_scale', 1)
        y_scale = options.get('y_scale', 1)
        url = options.get('url', None)
        tip = options.get('tip', None)
        anchor = options.get('positioning', None)
        image_data = options.get('image_data', None)

        if not image_data and not os.path.exists(filename):
            warn("Image file '%s' not found." % force_unicode(filename))
            return -1

        self.images.append([row, col, filename, x_offset, y_offset,
                            x_scale, y_scale, url, tip, anchor, image_data])

    @convert_cell_args
    def insert_textbox(self, row, col, text, options=None):
        """
        Insert an textbox with its top-left corner in a worksheet cell.

        Args:
            row:      The cell row (zero indexed).
            col:      The cell column (zero indexed).
            text:     The text for the textbox.
            options:  Textbox options.

        Returns:
            0:  Success.
            -1: Row or column is out of worksheet bounds.

        """
        # Check insert (row, col) without storing.
        if self._check_dimensions(row, col, True, True):
            warn('Cannot insert textbox at (%d, %d).' % (row, col))
            return -1

        if options is None:
            options = {}

        x_offset = options.get('x_offset', 0)
        y_offset = options.get('y_offset', 0)
        x_scale = options.get('x_scale', 1)
        y_scale = options.get('y_scale', 1)

        self.shapes.append([row, col, x_offset, y_offset,
                            x_scale, y_scale, text, options])

    @convert_cell_args
    def insert_chart(self, row, col, chart, options=None):
        """
        Insert an chart with its top-left corner in a worksheet cell.

        Args:
            row:     The cell row (zero indexed).
            col:     The cell column (zero indexed).
            chart:   Chart object.
            options: Position and scale of the chart.

        Returns:
            0:  Success.
            -1: Row or column is out of worksheet bounds.

        """
        # Check insert (row, col) without storing.
        if self._check_dimensions(row, col, True, True):
            warn('Cannot insert chart at (%d, %d).' % (row, col))
            return -1

        if options is None:
            options = {}

        # Ensure a chart isn't inserted more than once.
        if (chart.already_inserted or chart.combined
                and chart.combined.already_inserted):

            warn('Chart cannot be inserted in a worksheet more than once.')
            return
        else:
            chart.already_inserted = True

            if chart.combined:
                chart.combined.already_inserted = True

        x_offset = options.get('x_offset', 0)
        y_offset = options.get('y_offset', 0)
        x_scale = options.get('x_scale', 1)
        y_scale = options.get('y_scale', 1)

        # Allow Chart to override the scale and offset.
        if chart.x_scale != 1:
            x_scale = chart.x_scale

        if chart.y_scale != 1:
            y_scale = chart.y_scale

        if chart.x_offset:
            x_offset = chart.x_offset

        if chart.y_offset:
            y_offset = chart.y_offset

        self.charts.append([row, col, chart,
                            x_offset, y_offset,
                            x_scale, y_scale])

    @convert_cell_args
    def write_comment(self, row, col, comment, options=None):
        """
        Write a comment to a worksheet cell.

        Args:
            row:     The cell row (zero indexed).
            col:     The cell column (zero indexed).
            comment: Cell comment. Str.
            options: Comment formatting options.

        Returns:
            0:  Success.
            -1: Row or column is out of worksheet bounds.
            -2: String longer than 32k characters.

        """
        if options is None:
            options = {}

        # Check that row and col are valid and store max and min values
        if self._check_dimensions(row, col):
            return -1

        # Check that the comment string is < 32767 chars.
        if len(comment) > self.xls_strmax:
            return -2

        self.has_vml = 1
        self.has_comments = 1

        # Process the properties of the cell comment.
        self.comments[row][col] = \
            self._comment_params(row, col, comment, options)

    def show_comments(self):
        """
        Make any comments in the worksheet visible.

        Args:
            None.

        Returns:
            Nothing.

        """
        self.comments_visible = 1

    def set_comments_author(self, author):
        """
        Set the default author of the cell comments.

        Args:
            author: Comment author name. String.

        Returns:
            Nothing.

        """
        self.comments_author = author

    def get_name(self):
        """
        Retrieve the worksheet name.

        Args:
            None.

        Returns:
            Nothing.

        """
        # There is no set_name() method. Name must be set in add_worksheet().
        return self.name

    def activate(self):
        """
        Set this worksheet as the active worksheet, i.e. the worksheet that is
        displayed when the workbook is opened. Also set it as selected.

        Note: An active worksheet cannot be hidden.

        Args:
            None.

        Returns:
            Nothing.

        """
        self.hidden = 0
        self.selected = 1
        self.worksheet_meta.activesheet = self.index

    def select(self):
        """
        Set current worksheet as a selected worksheet, i.e. the worksheet
        has its tab highlighted.

        Note: A selected worksheet cannot be hidden.

        Args:
            None.

        Returns:
            Nothing.

        """
        self.selected = 1
        self.hidden = 0

    def hide(self):
        """
        Hide the current worksheet.

        Args:
            None.

        Returns:
            Nothing.

        """
        self.hidden = 1

        # A hidden worksheet shouldn't be active or selected.
        self.selected = 0

        # TODO. Should add a check to see if the sheet is the global
        # activesheet or firstsheet and reset them.

    def set_first_sheet(self):
        """
        Set current worksheet as the first visible sheet. This is necessary
        when there are a large number of worksheets and the activated
        worksheet is not visible on the screen.

        Note: A selected worksheet cannot be hidden.

        Args:
            None.

        Returns:
            Nothing.

        """
        self.hidden = 0  # Active worksheet can't be hidden.
        self.worksheet_meta.firstsheet = self.index

    @convert_column_args
    def set_column(self, first_col, last_col, width=None, cell_format=None,
                   options=None):
        """
        Set the width, and other properties of a single column or a
        range of columns.

        Args:
            first_col:    First column (zero-indexed).
            last_col:     Last column (zero-indexed). Can be same as first_col.
            width:       Column width. (optional).
            cell_format: Column cell_format. (optional).
            options:     Dict of options such as hidden and level.

        Returns:
            0:  Success.
            -1: Column number is out of worksheet bounds.

        """
        if options is None:
            options = {}

        # Ensure 2nd col is larger than first.
        if first_col > last_col:
            (first_col, last_col) = (last_col, first_col)

        # Don't modify the row dimensions when checking the columns.
        ignore_row = True

        # Set optional column values.
        hidden = options.get('hidden', False)
        collapsed = options.get('collapsed', False)
        level = options.get('level', 0)
        # Store the column dimension only in some conditions.
        if cell_format or (width and hidden):
            ignore_col = False
        else:
            ignore_col = True

        # Check that each column is valid and store the max and min values.
        if self._check_dimensions(0, last_col, ignore_row, ignore_col):
            return -1
        if self._check_dimensions(0, first_col, ignore_row, ignore_col):
            return -1

        # Set the limits for the outline levels (0 <= x <= 7).
        if level < 0:
            level = 0
        if level > 7:
            level = 7

        if level > self.outline_col_level:
            self.outline_col_level = level

        # Store the column data. Padded for sorting.
        self.colinfo["%05d" % first_col] = [first_col, last_col, width,
                                            cell_format, hidden, level,
                                            collapsed]

        # Store the column change to allow optimizations.
        self.col_size_changed = True

        # Store the col sizes for use when calculating image vertices taking
        # hidden columns into account. Also store the column formats.

        # Set width to zero if col is hidden
        if hidden:
            width = 0

        for col in range(first_col, last_col + 1):
            self.col_sizes[col] = width
            if cell_format:
                self.col_formats[col] = cell_format

        return 0

    def set_row(self, row, height=None, cell_format=None, options=None):
        """
        Set the width, and other properties of a row.

        Args:
            row:         Row number (zero-indexed).
            height:      Row width. (optional).
            cell_format: Row cell_format. (optional).
            options:     Dict of options such as hidden, level and collapsed.

        Returns:
            0:  Success.
            -1: Row number is out of worksheet bounds.

        """
        if options is None:
            options = {}

        # Use minimum col in _check_dimensions().
        if self.dim_colmin is not None:
            min_col = self.dim_colmin
        else:
            min_col = 0

        # Check that row is valid.
        if self._check_dimensions(row, min_col):
            return -1

        if height is None:
            height = self.default_row_height

        # Set optional row values.
        hidden = options.get('hidden', False)
        collapsed = options.get('collapsed', False)
        level = options.get('level', 0)

        # If the height is 0 the row is hidden and the height is the default.
        if height == 0:
            hidden = 1
            height = self.default_row_height

        # Set the limits for the outline levels (0 <= x <= 7).
        if level < 0:
            level = 0
        if level > 7:
            level = 7

        if level > self.outline_row_level:
            self.outline_row_level = level

        # Store the row properties.
        self.set_rows[row] = [height, cell_format, hidden, level, collapsed]

        # Store the row change to allow optimizations.
        self.row_size_changed = True

        if hidden:
            height = 0

        # Store the row sizes for use when calculating image vertices.
        self.row_sizes[row] = height

    def set_default_row(self, height=None, hide_unused_rows=False):
        """
        Set the default row properties.

        Args:
            height:           Default height. Optional, defaults to 15.
            hide_unused_rows: Hide unused rows. Optional, defaults to False.

        Returns:
            Nothing.

        """
        if height is None:
            height = self.default_row_height

        if height != self.original_row_height:
            # Store the row change to allow optimizations.
            self.row_size_changed = True
            self.default_row_height = height

        if hide_unused_rows:
            self.default_row_zeroed = 1

    @convert_range_args
    def merge_range(self, first_row, first_col, last_row, last_col,
                    data, cell_format=None):
        """
        Merge a range of cells.

        Args:
            first_row:    The first row of the cell range. (zero indexed).
            first_col:    The first column of the cell range.
            last_row:     The last row of the cell range. (zero indexed).
            last_col:     The last column of the cell range.
            data:         Cell data.
            cell_format:  Cell Format object.

        Returns:
             0:    Success.
            -1:    Row or column is out of worksheet bounds.
            other: Return value of write().

        """
        # Merge a range of cells. The first cell should contain the data and
        # the others should be blank. All cells should have the same format.

        # Excel doesn't allow a single cell to be merged
        if first_row == last_row and first_col == last_col:
            warn("Can't merge single cell")
            return

        # Swap last row/col with first row/col as necessary
        if first_row > last_row:
            (first_row, last_row) = (last_row, first_row)
        if first_col > last_col:
            (first_col, last_col) = (last_col, first_col)

        # Check that column number is valid and store the max value
        if self._check_dimensions(last_row, last_col) == -1:
            return

        # Store the merge range.
        self.merge.append([first_row, first_col, last_row, last_col])

        # Write the first cell
        self._write(first_row, first_col, data, cell_format)

        # Pad out the rest of the area with formatted blank cells.
        for row in range(first_row, last_row + 1):
            for col in range(first_col, last_col + 1):
                if row == first_row and col == first_col:
                    continue
                self._write_blank(row, col, '', cell_format)

    @convert_range_args
    def autofilter(self, first_row, first_col, last_row, last_col):
        """
        Set the autofilter area in the worksheet.

        Args:
            first_row:    The first row of the cell range. (zero indexed).
            first_col:    The first column of the cell range.
            last_row:     The last row of the cell range. (zero indexed).
            last_col:     The last column of the cell range.

        Returns:
             Nothing.

        """
        # Reverse max and min values if necessary.
        if last_row < first_row:
            (first_row, last_row) = (last_row, first_row)
        if last_col < first_col:
            (first_col, last_col) = (last_col, first_col)

        # Build up the print area range "Sheet1!$A$1:$C$13".
        area = self._convert_name_area(first_row, first_col,
                                       last_row, last_col)
        ref = xl_range(first_row, first_col, last_row, last_col)

        self.autofilter_area = area
        self.autofilter_ref = ref
        self.filter_range = [first_col, last_col]

    def filter_column(self, col, criteria):
        """
        Set the column filter criteria.

        Args:
            col:       Filter column (zero-indexed).
            criteria:  Filter criteria.

        Returns:
             Nothing.

        """
        if not self.autofilter_area:
            warn("Must call autofilter() before filter_column()")
            return

        # Check for a column reference in A1 notation and substitute.
        try:
            int(col)
        except ValueError:
            # Convert col ref to a cell ref and then to a col number.
            col_letter = col
            (_, col) = xl_cell_to_rowcol(col + '1')

            if col >= self.xls_colmax:
                warn("Invalid column '%s'" % col_letter)
                return

        (col_first, col_last) = self.filter_range

        # Reject column if it is outside filter range.
        if col < col_first or col > col_last:
            warn("Column '%d' outside autofilter() column range (%d, %d)"
                 % (col, col_first, col_last))
            return

        tokens = self._extract_filter_tokens(criteria)

        if not (len(tokens) == 3 or len(tokens) == 7):
            warn("Incorrect number of tokens in criteria '%s'" % criteria)

        tokens = self._parse_filter_expression(criteria, tokens)

        # Excel handles single or double custom filters as default filters.
        #  We need to check for them and handle them accordingly.
        if len(tokens) == 2 and tokens[0] == 2:
            # Single equality.
            self.filter_column_list(col, [tokens[1]])
        elif (len(tokens) == 5 and tokens[0] == 2 and tokens[2] == 1
              and tokens[3] == 2):
            # Double equality with "or" operator.
            self.filter_column_list(col, [tokens[1], tokens[4]])
        else:
            # Non default custom filter.
            self.filter_cols[col] = tokens
            self.filter_type[col] = 0

        self.filter_on = 1

    def filter_column_list(self, col, filters):
        """
        Set the column filter criteria in Excel 2007 list style.

        Args:
            col:      Filter column (zero-indexed).
            filters:  List of filter criteria to match.

        Returns:
             Nothing.

        """
        if not self.autofilter_area:
            warn("Must call autofilter() before filter_column()")
            return

        # Check for a column reference in A1 notation and substitute.
        try:
            int(col)
        except ValueError:
            # Convert col ref to a cell ref and then to a col number.
            col_letter = col
            (_, col) = xl_cell_to_rowcol(col + '1')

            if col >= self.xls_colmax:
                warn("Invalid column '%s'" % col_letter)
                return

        (col_first, col_last) = self.filter_range

        # Reject column if it is outside filter range.
        if col < col_first or col > col_last:
            warn("Column '%d' outside autofilter() column range "
                 "(%d,%d)" % (col, col_first, col_last))
            return

        self.filter_cols[col] = filters
        self.filter_type[col] = 1
        self.filter_on = 1

    @convert_range_args
    def data_validation(self, first_row, first_col, last_row, last_col,
                        options=None):
        """
        Add a data validation to a worksheet.

        Args:
            first_row:    The first row of the cell range. (zero indexed).
            first_col:    The first column of the cell range.
            last_row:     The last row of the cell range. (zero indexed).
            last_col:     The last column of the cell range.
            options:      Data validation options.

        Returns:
            0:  Success.
            -1: Row or column is out of worksheet bounds.
            -2: Incorrect parameter or option.
        """
        # Check that row and col are valid without storing the values.
        if self._check_dimensions(first_row, first_col, True, True):
            return -1
        if self._check_dimensions(last_row, last_col, True, True):
            return -1

        if options is None:
            options = {}
        else:
            # Copy the user defined options so they aren't modified.
            options = options.copy()

        # Valid input parameters.
        valid_parameters = {
            'validate': True,
            'criteria': True,
            'value': True,
            'source': True,
            'minimum': True,
            'maximum': True,
            'ignore_blank': True,
            'dropdown': True,
            'show_input': True,
            'input_title': True,
            'input_message': True,
            'show_error': True,
            'error_title': True,
            'error_message': True,
            'error_type': True,
            'other_cells': True,
        }

        # Check for valid input parameters.
        for param_key in options.keys():
            if param_key not in valid_parameters:
                warn("Unknown parameter '%s' in data_validation()" % param_key)
                return -2

        # Map alternative parameter names 'source' or 'minimum' to 'value'.
        if 'source' in options:
            options['value'] = options['source']
        if 'minimum' in options:
            options['value'] = options['minimum']

        # 'validate' is a required parameter.
        if 'validate' not in options:
            warn("Parameter 'validate' is required in data_validation()")
            return -2

        # List of  valid validation types.
        valid_types = {
            'any': 'none',
            'any value': 'none',
            'whole number': 'whole',
            'whole': 'whole',
            'integer': 'whole',
            'decimal': 'decimal',
            'list': 'list',
            'date': 'date',
            'time': 'time',
            'text length': 'textLength',
            'length': 'textLength',
            'custom': 'custom',
        }

        # Check for valid validation types.
        if not options['validate'] in valid_types:
            warn("Unknown validation type '%s' for parameter "
                 "'validate' in data_validation()" % options['validate'])
            return -2
        else:
            options['validate'] = valid_types[options['validate']]

        # No action is required for validation type 'any' if there are no
        # input messages to display.
        if (options['validate'] == 'none'
                and options.get('input_title') is None
                and options.get('input_message') is None):
            return -2

        # The any, list and custom validations don't have a criteria so we use
        # a default of 'between'.
        if (options['validate'] == 'none'
                or options['validate'] == 'list'
                or options['validate'] == 'custom'):
            options['criteria'] = 'between'
            options['maximum'] = None

        # 'criteria' is a required parameter.
        if 'criteria' not in options:
            warn("Parameter 'criteria' is required in data_validation()")
            return -2

        # Valid criteria types.
        criteria_types = {
            'between': 'between',
            'not between': 'notBetween',
            'equal to': 'equal',
            '=': 'equal',
            '==': 'equal',
            'not equal to': 'notEqual',
            '!=': 'notEqual',
            '<>': 'notEqual',
            'greater than': 'greaterThan',
            '>': 'greaterThan',
            'less than': 'lessThan',
            '<': 'lessThan',
            'greater than or equal to': 'greaterThanOrEqual',
            '>=': 'greaterThanOrEqual',
            'less than or equal to': 'lessThanOrEqual',
            '<=': 'lessThanOrEqual',
        }

        # Check for valid criteria types.
        if not options['criteria'] in criteria_types:
            warn("Unknown criteria type '%s' for parameter "
                 "'criteria' in data_validation()" % options['criteria'])
            return -2
        else:
            options['criteria'] = criteria_types[options['criteria']]

        # 'Between' and 'Not between' criteria require 2 values.
        if (options['criteria'] == 'between' or
                options['criteria'] == 'notBetween'):
            if 'maximum' not in options:
                warn("Parameter 'maximum' is required in data_validation() "
                     "when using 'between' or 'not between' criteria")
                return -2
        else:
            options['maximum'] = None

        # Valid error dialog types.
        error_types = {
            'stop': 0,
            'warning': 1,
            'information': 2,
        }

        # Check for valid error dialog types.
        if 'error_type' not in options:
            options['error_type'] = 0
        elif not options['error_type'] in error_types:
            warn("Unknown criteria type '%s' for parameter 'error_type' "
                 "in data_validation()" % options['error_type'])
            return -2
        else:
            options['error_type'] = error_types[options['error_type']]

        # Convert date/times value if required.
        if options['validate'] == 'date' or options['validate'] == 'time':

            if options['value']:
                if supported_datetime(options['value']):
                    date_time = self._convert_date_time(options['value'])
                    # Format date number to the same precision as Excel.
                    options['value'] = "%.16g" % date_time

            if options['maximum']:
                if supported_datetime(options['maximum']):
                    date_time = self._convert_date_time(options['maximum'])
                    options['maximum'] = "%.16g" % date_time

        # Check that the input title doesn't exceed the maximum length.
        if options.get('input_title') and len(options['input_title']) > 32:
            warn("Length of input title '%s' exceeds Excel's limit of 32"
                 % force_unicode(options['input_title']))
            return -2

        # Check that the error title doesn't exceed the maximum length.
        if options.get('error_title') and len(options['error_title']) > 32:
            warn("Length of error title '%s' exceeds Excel's limit of 32"
                 % force_unicode(options['error_title']))
            return -2

        # Check that the input message doesn't exceed the maximum length.
        if (options.get('input_message')
                and len(options['input_message']) > 255):
            warn("Length of input message '%s' exceeds Excel's limit of 255"
                 % force_unicode(options['input_message']))
            return -2

        # Check that the error message doesn't exceed the maximum length.
        if (options.get('error_message')
                and len(options['error_message']) > 255):
            warn("Length of error message '%s' exceeds Excel's limit of 255"
                 % force_unicode(options['error_message']))
            return -2

        # Check that the input list doesn't exceed the maximum length.
        if options['validate'] == 'list' and type(options['value']) is list:
            formula = self._csv_join(*options['value'])
            if len(formula) > 255:
                warn("Length of list items '%s' exceeds Excel's limit of "
                     "255, use a formula range instead"
                     % force_unicode(formula))
                return -2

        # Set some defaults if they haven't been defined by the user.
        if 'ignore_blank' not in options:
            options['ignore_blank'] = 1
        if 'dropdown' not in options:
            options['dropdown'] = 1
        if 'show_input' not in options:
            options['show_input'] = 1
        if 'show_error' not in options:
            options['show_error'] = 1

        # These are the cells to which the validation is applied.
        options['cells'] = [[first_row, first_col, last_row, last_col]]

        # A (for now) undocumented parameter to pass additional cell ranges.
        if 'other_cells' in options:
            options['cells'].extend(options['other_cells'])

        # Store the validation information until we close the worksheet.
        self.validations.append(options)

    @convert_range_args
    def conditional_format(self, first_row, first_col, last_row, last_col,
                           options=None):
        """
        Add a conditional format to a worksheet.

        Args:
            first_row:    The first row of the cell range. (zero indexed).
            first_col:    The first column of the cell range.
            last_row:     The last row of the cell range. (zero indexed).
            last_col:     The last column of the cell range.
            options:      Conditional format options.

        Returns:
            0:  Success.
            -1: Row or column is out of worksheet bounds.
            -2: Incorrect parameter or option.
        """
        # Check that row and col are valid without storing the values.
        if self._check_dimensions(first_row, first_col, True, True):
            return -1
        if self._check_dimensions(last_row, last_col, True, True):
            return -1

        if options is None:
            options = {}
        else:
            # Copy the user defined options so they aren't modified.
            options = options.copy()

        # Valid input parameters.
        valid_parameter = {
            'type': True,
            'format': True,
            'criteria': True,
            'value': True,
            'minimum': True,
            'maximum': True,
            'stop_if_true': True,
            'min_type': True,
            'mid_type': True,
            'max_type': True,
            'min_value': True,
            'mid_value': True,
            'max_value': True,
            'min_color': True,
            'mid_color': True,
            'max_color': True,
            'min_length': True,
            'max_length': True,
            'multi_range': True,
            'bar_color': True,
            'bar_negative_color': True,
            'bar_negative_color_same': True,
            'bar_solid': True,
            'bar_border_color': True,
            'bar_negative_border_color': True,
            'bar_negative_border_color_same': True,
            'bar_no_border': True,
            'bar_direction': True,
            'bar_axis_position': True,
            'bar_axis_color': True,
            'bar_only': True,
            'data_bar_2010': True,
            'icon_style': True,
            'reverse_icons': True,
            'icons_only': True,
            'icons': True}

        # Check for valid input parameters.
        for param_key in options.keys():
            if param_key not in valid_parameter:
                warn("Unknown parameter '%s' in conditional_format()" %
                     param_key)
                return -2

        # 'type' is a required parameter.
        if 'type' not in options:
            warn("Parameter 'type' is required in conditional_format()")
            return -2

        # Valid types.
        valid_type = {
            'cell': 'cellIs',
            'date': 'date',
            'time': 'time',
            'average': 'aboveAverage',
            'duplicate': 'duplicateValues',
            'unique': 'uniqueValues',
            'top': 'top10',
            'bottom': 'top10',
            'text': 'text',
            'time_period': 'timePeriod',
            'blanks': 'containsBlanks',
            'no_blanks': 'notContainsBlanks',
            'errors': 'containsErrors',
            'no_errors': 'notContainsErrors',
            '2_color_scale': '2_color_scale',
            '3_color_scale': '3_color_scale',
            'data_bar': 'dataBar',
            'formula': 'expression',
            'icon_set': 'iconSet'}

        # Check for valid types.
        if options['type'] not in valid_type:
            warn("Unknown value '%s' for parameter 'type' "
                 "in conditional_format()" % options['type'])
            return -2
        else:
            if options['type'] == 'bottom':
                options['direction'] = 'bottom'
            options['type'] = valid_type[options['type']]

        # Valid criteria types.
        criteria_type = {
            'between': 'between',
            'not between': 'notBetween',
            'equal to': 'equal',
            '=': 'equal',
            '==': 'equal',
            'not equal to': 'notEqual',
            '!=': 'notEqual',
            '<>': 'notEqual',
            'greater than': 'greaterThan',
            '>': 'greaterThan',
            'less than': 'lessThan',
            '<': 'lessThan',
            'greater than or equal to': 'greaterThanOrEqual',
            '>=': 'greaterThanOrEqual',
            'less than or equal to': 'lessThanOrEqual',
            '<=': 'lessThanOrEqual',
            'containing': 'containsText',
            'not containing': 'notContains',
            'begins with': 'beginsWith',
            'ends with': 'endsWith',
            'yesterday': 'yesterday',
            'today': 'today',
            'last 7 days': 'last7Days',
            'last week': 'lastWeek',
            'this week': 'thisWeek',
            'next week': 'nextWeek',
            'last month': 'lastMonth',
            'this month': 'thisMonth',
            'next month': 'nextMonth',
            # For legacy, but incorrect, support.
            'continue week': 'nextWeek',
            'continue month': 'nextMonth'}

        # Check for valid criteria types.
        if 'criteria' in options and options['criteria'] in criteria_type:
            options['criteria'] = criteria_type[options['criteria']]

        # Convert date/times value if required.
        if options['type'] == 'date' or options['type'] == 'time':
            options['type'] = 'cellIs'

            if 'value' in options:
                if not supported_datetime(options['value']):
                    warn("Conditional format 'value' must be a "
                         "datetime object.")
                    return -2
                else:
                    date_time = self._convert_date_time(options['value'])
                    # Format date number to the same precision as Excel.
                    options['value'] = "%.16g" % date_time

            if 'minimum' in options:
                if not supported_datetime(options['minimum']):
                    warn("Conditional format 'minimum' must be a "
                         "datetime object.")
                    return -2
                else:
                    date_time = self._convert_date_time(options['minimum'])
                    options['minimum'] = "%.16g" % date_time

            if 'maximum' in options:
                if not supported_datetime(options['maximum']):
                    warn("Conditional format 'maximum' must be a "
                         "datetime object.")
                    return -2
                else:
                    date_time = self._convert_date_time(options['maximum'])
                    options['maximum'] = "%.16g" % date_time

        # Valid icon styles.
        valid_icons = {
            "3_arrows": "3Arrows",                          # 1
            "3_flags": "3Flags",                            # 2
            "3_traffic_lights_rimmed": "3TrafficLights2",   # 3
            "3_symbols_circled": "3Symbols",                # 4
            "4_arrows": "4Arrows",                          # 5
            "4_red_to_black": "4RedToBlack",                # 6
            "4_traffic_lights": "4TrafficLights",           # 7
            "5_arrows_gray": "5ArrowsGray",                 # 8
            "5_quarters": "5Quarters",                      # 9
            "3_arrows_gray": "3ArrowsGray",                 # 10
            "3_traffic_lights": "3TrafficLights",           # 11
            "3_signs": "3Signs",                            # 12
            "3_symbols": "3Symbols2",                       # 13
            "4_arrows_gray": "4ArrowsGray",                 # 14
            "4_ratings": "4Rating",                         # 15
            "5_arrows": "5Arrows",                          # 16
            "5_ratings": "5Rating"}                         # 17

        # Set the icon set properties.
        if options['type'] == 'iconSet':

            # An icon_set must have an icon style.
            if not options.get('icon_style'):
                warn("The 'icon_style' parameter must be specified when "
                     "'type' == 'icon_set' in conditional_format()")
                return -3

            # Check for valid icon styles.
            if options['icon_style'] not in valid_icons:
                warn("Unknown icon_style '%s' in conditional_format()" %
                     options['icon_style'])
                return -2
            else:
                options['icon_style'] = valid_icons[options['icon_style']]

            # Set the number of icons for the icon style.
            options['total_icons'] = 3
            if options['icon_style'].startswith('4'):
                options['total_icons'] = 4
            elif options['icon_style'].startswith('5'):
                options['total_icons'] = 5

            options['icons'] = self._set_icon_props(options.get('total_icons'),
                                                    options.get('icons'))

        # Swap last row/col for first row/col as necessary
        if first_row > last_row:
            first_row, last_row = last_row, first_row

        if first_col > last_col:
            first_col, last_col = last_col, first_col

        # Set the formatting range.
        # If the first and last cell are the same write a single cell.
        if first_row == last_row and first_col == last_col:
            cell_range = xl_rowcol_to_cell(first_row, first_col)
            start_cell = cell_range
        else:
            cell_range = xl_range(first_row, first_col, last_row, last_col)
            start_cell = xl_rowcol_to_cell(first_row, first_col)

        # Override with user defined multiple range if provided.
        if 'multi_range' in options:
            cell_range = options['multi_range']
            cell_range = cell_range.replace('$', '')

        # Get the dxf format index.
        if 'format' in options and options['format']:
            options['format'] = options['format']._get_dxf_index()

        # Set the priority based on the order of adding.
        options['priority'] = self.dxf_priority
        self.dxf_priority += 1

        # Check for 2010 style data_bar parameters.
        if (self.use_data_bars_2010 or
                options.get('data_bar_2010') or
                options.get('bar_solid') or
                options.get('bar_border_color') or
                options.get('bar_negative_color') or
                options.get('bar_negative_color_same') or
                options.get('bar_negative_border_color') or
                options.get('bar_negative_border_color_same') or
                options.get('bar_no_border') or
                options.get('bar_axis_position') or
                options.get('bar_axis_color') or
                options.get('bar_direction')):
            options['is_data_bar_2010'] = True

        # Special handling of text criteria.
        if options['type'] == 'text':

            if options['criteria'] == 'containsText':
                options['type'] = 'containsText'
                options['formula'] = ('NOT(ISERROR(SEARCH("%s",%s)))'
                                      % (options['value'], start_cell))
            elif options['criteria'] == 'notContains':
                options['type'] = 'notContainsText'
                options['formula'] = ('ISERROR(SEARCH("%s",%s))'
                                      % (options['value'], start_cell))
            elif options['criteria'] == 'beginsWith':
                options['type'] = 'beginsWith'
                options['formula'] = ('LEFT(%s,%d)="%s"'
                                      % (start_cell,
                                         len(options['value']),
                                         options['value']))
            elif options['criteria'] == 'endsWith':
                options['type'] = 'endsWith'
                options['formula'] = ('RIGHT(%s,%d)="%s"'
                                      % (start_cell,
                                         len(options['value']),
                                         options['value']))
            else:
                warn("Invalid text criteria '%s' "
                     "in conditional_format()" % options['criteria'])

        # Special handling of time time_period criteria.
        if options['type'] == 'timePeriod':

            if options['criteria'] == 'yesterday':
                options['formula'] = 'FLOOR(%s,1)=TODAY()-1' % start_cell

            elif options['criteria'] == 'today':
                options['formula'] = 'FLOOR(%s,1)=TODAY()' % start_cell

            elif options['criteria'] == 'tomorrow':
                options['formula'] = 'FLOOR(%s,1)=TODAY()+1' % start_cell

            elif options['criteria'] == 'last7Days':
                options['formula'] = \
                    ('AND(TODAY()-FLOOR(%s,1)<=6,FLOOR(%s,1)<=TODAY())' %
                     (start_cell, start_cell))

            elif options['criteria'] == 'lastWeek':
                options['formula'] = \
                    ('AND(TODAY()-ROUNDDOWN(%s,0)>=(WEEKDAY(TODAY())),'
                     'TODAY()-ROUNDDOWN(%s,0)<(WEEKDAY(TODAY())+7))' %
                     (start_cell, start_cell))

            elif options['criteria'] == 'thisWeek':
                options['formula'] = \
                    ('AND(TODAY()-ROUNDDOWN(%s,0)<=WEEKDAY(TODAY())-1,'
                     'ROUNDDOWN(%s,0)-TODAY()<=7-WEEKDAY(TODAY()))' %
                     (start_cell, start_cell))

            elif options['criteria'] == 'nextWeek':
                options['formula'] = \
                    ('AND(ROUNDDOWN(%s,0)-TODAY()>(7-WEEKDAY(TODAY())),'
                     'ROUNDDOWN(%s,0)-TODAY()<(15-WEEKDAY(TODAY())))' %
                     (start_cell, start_cell))

            elif options['criteria'] == 'lastMonth':
                options['formula'] = \
                    ('AND(MONTH(%s)=MONTH(TODAY())-1,OR(YEAR(%s)=YEAR('
                     'TODAY()),AND(MONTH(%s)=1,YEAR(A1)=YEAR(TODAY())-1)))' %
                     (start_cell, start_cell, start_cell))

            elif options['criteria'] == 'thisMonth':
                options['formula'] = \
                    ('AND(MONTH(%s)=MONTH(TODAY()),YEAR(%s)=YEAR(TODAY()))' %
                     (start_cell, start_cell))

            elif options['criteria'] == 'nextMonth':
                options['formula'] = \
                    ('AND(MONTH(%s)=MONTH(TODAY())+1,OR(YEAR(%s)=YEAR('
                     'TODAY()),AND(MONTH(%s)=12,YEAR(%s)=YEAR(TODAY())+1)))' %
                     (start_cell, start_cell, start_cell, start_cell))

            else:
                warn("Invalid time_period criteria '%s' "
                     "in conditional_format()" % options['criteria'])

        # Special handling of blanks/error types.
        if options['type'] == 'containsBlanks':
            options['formula'] = 'LEN(TRIM(%s))=0' % start_cell

        if options['type'] == 'notContainsBlanks':
            options['formula'] = 'LEN(TRIM(%s))>0' % start_cell

        if options['type'] == 'containsErrors':
            options['formula'] = 'ISERROR(%s)' % start_cell

        if options['type'] == 'notContainsErrors':
            options['formula'] = 'NOT(ISERROR(%s))' % start_cell

        # Special handling for 2 color scale.
        if options['type'] == '2_color_scale':
            options['type'] = 'colorScale'

            # Color scales don't use any additional formatting.
            options['format'] = None

            # Turn off 3 color parameters.
            options['mid_type'] = None
            options['mid_color'] = None

            options.setdefault('min_type', 'min')
            options.setdefault('max_type', 'max')
            options.setdefault('min_value', 0)
            options.setdefault('max_value', 0)
            options.setdefault('min_color', '#FF7128')
            options.setdefault('max_color', '#FFEF9C')

            options['min_color'] = xl_color(options['min_color'])
            options['max_color'] = xl_color(options['max_color'])

        # Special handling for 3 color scale.
        if options['type'] == '3_color_scale':
            options['type'] = 'colorScale'

            # Color scales don't use any additional formatting.
            options['format'] = None

            options.setdefault('min_type', 'min')
            options.setdefault('mid_type', 'percentile')
            options.setdefault('max_type', 'max')
            options.setdefault('min_value', 0)
            options.setdefault('max_value', 0)
            options.setdefault('min_color', '#F8696B')
            options.setdefault('mid_color', '#FFEB84')
            options.setdefault('max_color', '#63BE7B')

            options['min_color'] = xl_color(options['min_color'])
            options['mid_color'] = xl_color(options['mid_color'])
            options['max_color'] = xl_color(options['max_color'])

            # Set a default mid value.
            if 'mid_value' not in options:
                options['mid_value'] = 50

        # Special handling for data bar.
        if options['type'] == 'dataBar':

            # Color scales don't use any additional formatting.
            options['format'] = None

            if not options.get('min_type'):
                options['min_type'] = 'min'
                options['x14_min_type'] = 'autoMin'
            else:
                options['x14_min_type'] = options['min_type']

            if not options.get('max_type'):
                options['max_type'] = 'max'
                options['x14_max_type'] = 'autoMax'
            else:
                options['x14_max_type'] = options['max_type']

            options.setdefault('min_value', 0)
            options.setdefault('max_value', 0)
            options.setdefault('bar_color', '#638EC6')
            options.setdefault('bar_border_color', options['bar_color'])
            options.setdefault('bar_only', False)
            options.setdefault('bar_no_border', False)
            options.setdefault('bar_solid', False)
            options.setdefault('bar_direction', '')
            options.setdefault('bar_negative_color', '#FF0000')
            options.setdefault('bar_negative_border_color', '#FF0000')
            options.setdefault('bar_negative_color_same', False)
            options.setdefault('bar_negative_border_color_same', False)
            options.setdefault('bar_axis_position', '')
            options.setdefault('bar_axis_color', '#000000')

            options['bar_color'] = xl_color(options['bar_color'])
            options['bar_border_color'] = xl_color(options['bar_border_color'])
            options['bar_axis_color'] = xl_color(options['bar_axis_color'])
            options['bar_negative_color'] = \
                xl_color(options['bar_negative_color'])
            options['bar_negative_border_color'] = \
                xl_color(options['bar_negative_border_color'])

        # Adjust for 2010 style data_bar parameters.
        if options.get('is_data_bar_2010'):
            self.excel_version = 2010

            if options['min_type'] is 'min' and options['min_value'] == 0:
                options['min_value'] = None

            if options['max_type'] is 'max' and options['max_value'] == 0:
                options['max_value'] = None

            options['range'] = cell_range

        # Strip the leading = from formulas.
        try:
            options['min_value'] = options['min_value'].lstrip('=')
        except (KeyError, AttributeError):
            pass
        try:
            options['mid_value'] = options['mid_value'].lstrip('=')
        except (KeyError, AttributeError):
            pass
        try:
            options['max_value'] = options['max_value'].lstrip('=')
        except (KeyError, AttributeError):
            pass

        # Store the conditional format until we close the worksheet.
        if cell_range in self.cond_formats:
            self.cond_formats[cell_range].append(options)
        else:
            self.cond_formats[cell_range] = [options]

    @convert_range_args
    def add_table(self, first_row, first_col, last_row, last_col,
                  options=None):
        """
        Add an Excel table to a worksheet.

        Args:
            first_row:    The first row of the cell range. (zero indexed).
            first_col:    The first column of the cell range.
            last_row:     The last row of the cell range. (zero indexed).
            last_col:     The last column of the cell range.
            options:      Table format options. (Optional)

        Returns:
            0:  Success.
            -1: Not supported in constant_memory mode.
            -2: Row or column is out of worksheet bounds.
            -3: Incorrect parameter or option.
        """
        table = {}
        col_formats = {}

        if options is None:
            options = {}
        else:
            # Copy the user defined options so they aren't modified.
            options = options.copy()

        if self.constant_memory:
            warn("add_table() isn't supported in 'constant_memory' mode")
            return -1

        # Check that row and col are valid without storing the values.
        if self._check_dimensions(first_row, first_col, True, True):
            return -2
        if self._check_dimensions(last_row, last_col, True, True):
            return -2

        # Valid input parameters.
        valid_parameter = {
            'autofilter': True,
            'banded_columns': True,
            'banded_rows': True,
            'columns': True,
            'data': True,
            'first_column': True,
            'header_row': True,
            'last_column': True,
            'name': True,
            'style': True,
            'total_row': True,
        }

        # Check for valid input parameters.
        for param_key in options.keys():
            if param_key not in valid_parameter:
                warn("Unknown parameter '%s' in add_table()" % param_key)
                return -3

        # Turn on Excel's defaults.
        options['banded_rows'] = options.get('banded_rows', True)
        options['header_row'] = options.get('header_row', True)
        options['autofilter'] = options.get('autofilter', True)

        # Set the table options.
        table['show_first_col'] = options.get('first_column', False)
        table['show_last_col'] = options.get('last_column', False)
        table['show_row_stripes'] = options.get('banded_rows', False)
        table['show_col_stripes'] = options.get('banded_columns', False)
        table['header_row_count'] = options.get('header_row', 0)
        table['totals_row_shown'] = options.get('total_row', False)

        # Set the table name.
        if 'name' in options:
            name = options['name']
            table['name'] = name

            if ' ' in name:
                warn("Name '%s' in add_table() cannot contain spaces"
                     % force_unicode(name))
                return -3

            # Warn if the name contains invalid chars as defined by Excel.
            if (not re.match(r'^[\w\\][\w\\.]*$', name, re.UNICODE)
                    or re.match(r'^\d', name)):
                warn("Invalid Excel characters in add_table(): '%s'"
                     % force_unicode(name))
                return -1

            # Warn if the name looks like a cell name.
            if re.match(r'^[a-zA-Z][a-zA-Z]?[a-dA-D]?[0-9]+$', name):
                warn("Name looks like a cell name in add_table(): '%s'"
                     % force_unicode(name))
                return -1

            # Warn if the name looks like a R1C1 cell reference.
            if (re.match(r'^[rcRC]$', name)
                    or re.match(r'^[rcRC]\d+[rcRC]\d+$', name)):
                warn("Invalid name '%s' like a RC cell ref in add_table()"
                     % force_unicode(name))
                return -1

        # Set the table style.
        if 'style' in options:
            table['style'] = options['style']
            # Remove whitespace from style name.
            table['style'] = table['style'].replace(' ', '')
        else:
            table['style'] = "TableStyleMedium9"

        # Swap last row/col for first row/col as necessary.
        if first_row > last_row:
            (first_row, last_row) = (last_row, first_row)
        if first_col > last_col:
            (first_col, last_col) = (last_col, first_col)

        # Set the data range rows (without the header and footer).
        first_data_row = first_row
        last_data_row = last_row

        if options.get('header_row'):
            first_data_row += 1

        if options.get('total_row'):
            last_data_row -= 1

        # Set the table and autofilter ranges.
        table['range'] = xl_range(first_row, first_col,
                                  last_row, last_col)

        table['a_range'] = xl_range(first_row, first_col,
                                    last_data_row, last_col)

        # If the header row if off the default is to turn autofilter off.
        if not options['header_row']:
            options['autofilter'] = 0

        # Set the autofilter range.
        if options['autofilter']:
            table['autofilter'] = table['a_range']

        # Add the table columns.
        col_id = 1
        table['columns'] = []
        seen_names = {}

        for col_num in range(first_col, last_col + 1):
            # Set up the default column data.
            col_data = {
                'id': col_id,
                'name': 'Column' + str(col_id),
                'total_string': '',
                'total_function': '',
                'total_value': 0,
                'formula': '',
                'format': None,
                'name_format': None,
            }

            # Overwrite the defaults with any user defined values.
            if 'columns' in options:
                # Check if there are user defined values for this column.
                if col_id <= len(options['columns']):
                    user_data = options['columns'][col_id - 1]
                else:
                    user_data = None

                if user_data:
                    # Get the column format.
                    xformat = user_data.get('format', None)

                    # Map user defined values to internal values.
                    if user_data.get('header'):
                        col_data['name'] = user_data['header']

                    # Excel requires unique case insensitive header names.
                    header_name = col_data['name']
                    name = header_name.lower()
                    if name in seen_names:
                        warn("Duplicate header name in add_table(): '%s'"
                             % force_unicode(name))
                        return -1
                    else:
                        seen_names[name] = True

                    col_data['name_format'] = user_data.get('header_format')

                    # Handle the column formula.
                    if 'formula' in user_data and user_data['formula']:
                        formula = user_data['formula']

                        # Remove the formula '=' sign if it exists.
                        if formula.startswith('='):
                            formula = formula.lstrip('=')

                        # Covert Excel 2010 "@" ref to 2007 "#This Row".
                        formula = formula.replace('@', '[#This Row],')

                        col_data['formula'] = formula

                        for row in range(first_data_row, last_data_row + 1):
                            self._write_formula(row, col_num, formula, xformat)

                    # Handle the function for the total row.
                    if user_data.get('total_function'):
                        function = user_data['total_function']

                        # Massage the function name.
                        function = function.lower()
                        function = function.replace('_', '')
                        function = function.replace(' ', '')

                        if function == 'countnums':
                            function = 'countNums'
                        if function == 'stddev':
                            function = 'stdDev'

                        col_data['total_function'] = function

                        formula = \
                            self._table_function_to_formula(function,
                                                            col_data['name'])

                        value = user_data.get('total_value', 0)

                        self._write_formula(last_row, col_num, formula,
                                            xformat, value)

                    elif user_data.get('total_string'):
                        # Total label only (not a function).
                        total_string = user_data['total_string']
                        col_data['total_string'] = total_string

                        self._write_string(last_row, col_num, total_string,
                                           user_data.get('format'))

                    # Get the dxf format index.
                    if xformat is not None:
                        col_data['format'] = xformat._get_dxf_index()

                    # Store the column format for writing the cell data.
                    # It doesn't matter if it is undefined.
                    col_formats[col_id - 1] = xformat

            # Store the column data.
            table['columns'].append(col_data)

            # Write the column headers to the worksheet.
            if options['header_row']:
                self._write_string(first_row, col_num, col_data['name'],
                                   col_data['name_format'])

            col_id += 1

        # Write the cell data if supplied.
        if 'data' in options:
            data = options['data']

            i = 0  # For indexing the row data.
            for row in range(first_data_row, last_data_row + 1):
                j = 0  # For indexing the col data.
                for col in range(first_col, last_col + 1):
                    if i < len(data) and j < len(data[i]):
                        token = data[i][j]
                        if j in col_formats:
                            self._write(row, col, token, col_formats[j])
                        else:
                            self._write(row, col, token, None)
                    j += 1
                i += 1

        # Store the table data.
        self.tables.append(table)

        return table

    @convert_cell_args
    def add_sparkline(self, row, col, options=None):
        """
        Add sparklines to the worksheet.

        Args:
            row:     The cell row (zero indexed).
            col:     The cell column (zero indexed).
            options: Sparkline formatting options.

        Returns:
            0:  Success.
            -1: Row or column is out of worksheet bounds.
            -2: Incorrect parameter or option.

        """

        # Check that row and col are valid without storing the values.
        if self._check_dimensions(row, col, True, True):
            return -1

        sparkline = {'locations': [xl_rowcol_to_cell(row, col)]}

        if options is None:
            options = {}

        # Valid input parameters.
        valid_parameters = {
            'location': True,
            'range': True,
            'type': True,
            'high_point': True,
            'low_point': True,
            'negative_points': True,
            'first_point': True,
            'last_point': True,
            'markers': True,
            'style': True,
            'series_color': True,
            'negative_color': True,
            'markers_color': True,
            'first_color': True,
            'last_color': True,
            'high_color': True,
            'low_color': True,
            'max': True,
            'min': True,
            'axis': True,
            'reverse': True,
            'empty_cells': True,
            'show_hidden': True,
            'plot_hidden': True,
            'date_axis': True,
            'weight': True,
        }

        # Check for valid input parameters.
        for param_key in options.keys():
            if param_key not in valid_parameters:
                warn("Unknown parameter '%s' in add_sparkline()" % param_key)
                return -1

        # 'range' is a required parameter.
        if 'range' not in options:
            warn("Parameter 'range' is required in add_sparkline()")
            return -2

        # Handle the sparkline type.
        spark_type = options.get('type', 'line')

        if spark_type not in ('line', 'column', 'win_loss'):
            warn("Parameter 'type' must be 'line', 'column' "
                 "or 'win_loss' in add_sparkline()")
            return -2

        if spark_type == 'win_loss':
            spark_type = 'stacked'
        sparkline['type'] = spark_type

        # We handle single location/range values or list of values.
        if 'location' in options:
            if type(options['location']) is list:
                sparkline['locations'] = options['location']
            else:
                sparkline['locations'] = [options['location']]

        if type(options['range']) is list:
            sparkline['ranges'] = options['range']
        else:
            sparkline['ranges'] = [options['range']]

        range_count = len(sparkline['ranges'])
        location_count = len(sparkline['locations'])

        # The ranges and locations must match.
        if range_count != location_count:
            warn("Must have the same number of location and range "
                 "parameters in add_sparkline()")
            return -2

        # Store the count.
        sparkline['count'] = len(sparkline['locations'])

        # Get the worksheet name for the range conversion below.
        sheetname = quote_sheetname(self.name)

        # Cleanup the input ranges.
        new_ranges = []
        for spark_range in sparkline['ranges']:

            # Remove the absolute reference $ symbols.
            spark_range = spark_range.replace('$', '')

            # Remove the = from formula.
            spark_range = spark_range.lstrip('=')

            # Convert a simple range into a full Sheet1!A1:D1 range.
            if '!' not in spark_range:
                spark_range = sheetname + "!" + spark_range

            new_ranges.append(spark_range)

        sparkline['ranges'] = new_ranges

        # Cleanup the input locations.
        new_locations = []
        for location in sparkline['locations']:
            location = location.replace('$', '')
            new_locations.append(location)

        sparkline['locations'] = new_locations

        # Map options.
        sparkline['high'] = options.get('high_point')
        sparkline['low'] = options.get('low_point')
        sparkline['negative'] = options.get('negative_points')
        sparkline['first'] = options.get('first_point')
        sparkline['last'] = options.get('last_point')
        sparkline['markers'] = options.get('markers')
        sparkline['min'] = options.get('min')
        sparkline['max'] = options.get('max')
        sparkline['axis'] = options.get('axis')
        sparkline['reverse'] = options.get('reverse')
        sparkline['hidden'] = options.get('show_hidden')
        sparkline['weight'] = options.get('weight')

        # Map empty cells options.
        empty = options.get('empty_cells', '')

        if empty == 'zero':
            sparkline['empty'] = 0
        elif empty == 'connect':
            sparkline['empty'] = 'span'
        else:
            sparkline['empty'] = 'gap'

        # Map the date axis range.
        date_range = options.get('date_axis')

        if date_range and '!' not in date_range:
            date_range = sheetname + "!" + date_range

        sparkline['date_axis'] = date_range

        # Set the sparkline styles.
        style_id = options.get('style', 0)
        style = get_sparkline_style(style_id)

        sparkline['series_color'] = style['series']
        sparkline['negative_color'] = style['negative']
        sparkline['markers_color'] = style['markers']
        sparkline['first_color'] = style['first']
        sparkline['last_color'] = style['last']
        sparkline['high_color'] = style['high']
        sparkline['low_color'] = style['low']

        # Override the style colors with user defined colors.
        self._set_spark_color(sparkline, options, 'series_color')
        self._set_spark_color(sparkline, options, 'negative_color')
        self._set_spark_color(sparkline, options, 'markers_color')
        self._set_spark_color(sparkline, options, 'first_color')
        self._set_spark_color(sparkline, options, 'last_color')
        self._set_spark_color(sparkline, options, 'high_color')
        self._set_spark_color(sparkline, options, 'low_color')

        self.sparklines.append(sparkline)

    @convert_range_args
    def set_selection(self, first_row, first_col, last_row, last_col):
        """
        Set the selected cell or cells in a worksheet

        Args:
            first_row:    The first row of the cell range. (zero indexed).
            first_col:    The first column of the cell range.
            last_row:     The last row of the cell range. (zero indexed).
            last_col:     The last column of the cell range.

        Returns:
            0:  Nothing.
        """
        pane = None

        # Range selection. Do this before swapping max/min to allow the
        # selection direction to be reversed.
        active_cell = xl_rowcol_to_cell(first_row, first_col)

        # Swap last row/col for first row/col if necessary
        if first_row > last_row:
            (first_row, last_row) = (last_row, first_row)

        if first_col > last_col:
            (first_col, last_col) = (last_col, first_col)

        # If the first and last cell are the same write a single cell.
        if (first_row == last_row) and (first_col == last_col):
            sqref = active_cell
        else:
            sqref = xl_range(first_row, first_col, last_row, last_col)

        # Selection isn't set for cell A1.
        if sqref == 'A1':
            return

        self.selections = [[pane, active_cell, sqref]]

    def outline_settings(self, visible=1, symbols_below=1, symbols_right=1,
                         auto_style=0):
        """
        Control outline settings.

        Args:
            visible:       Outlines are visible. Optional, defaults to True.
            symbols_below: Show row outline symbols below the outline bar.
                           Optional, defaults to True.
            symbols_right: Show column outline symbols to the right of the
                           outline bar. Optional, defaults to True.
            auto_style:    Use Automatic style. Optional, defaults to False.

        Returns:
            0:  Nothing.
        """
        self.outline_on = visible
        self.outline_below = symbols_below
        self.outline_right = symbols_right
        self.outline_style = auto_style

        self.outline_changed = True

    @convert_cell_args
    def freeze_panes(self, row, col, top_row=None, left_col=None, pane_type=0):
        """
        Create worksheet panes and mark them as frozen.

        Args:
            row:      The cell row (zero indexed).
            col:      The cell column (zero indexed).
            top_row:  Topmost visible row in scrolling region of pane.
            left_col: Leftmost visible row in scrolling region of pane.

        Returns:
            0:  Nothing.

        """
        if top_row is None:
            top_row = row

        if left_col is None:
            left_col = col

        self.panes = [row, col, top_row, left_col, pane_type]

    @convert_cell_args
    def split_panes(self, x, y, top_row=None, left_col=None):
        """
        Create worksheet panes and mark them as split.

        Args:
            x:        The position for the vertical split.
            y:        The position for the horizontal split.
            top_row:  Topmost visible row in scrolling region of pane.
            left_col: Leftmost visible row in scrolling region of pane.

        Returns:
            0:  Nothing.

        """
        # Same as freeze panes with a different pane type.
        self.freeze_panes(x, y, top_row, left_col, 2)

    def set_zoom(self, zoom=100):
        """
        Set the worksheet zoom factor.

        Args:
            zoom: Scale factor: 10 <= zoom <= 400.

        Returns:
            Nothing.

        """
        # Ensure the zoom scale is in Excel's range.
        if zoom < 10 or zoom > 400:
            warn("Zoom factor %d outside range: 10 <= zoom <= 400" % zoom)
            zoom = 100

        self.zoom = int(zoom)

    def right_to_left(self):
        """
        Display the worksheet right to left for some versions of Excel.

        Args:
            None.

        Returns:
            Nothing.

        """
        self.is_right_to_left = 1

    def hide_zero(self):
        """
        Hide zero values in worksheet cells.

        Args:
            None.

        Returns:
            Nothing.

        """
        self.show_zeros = 0

    def set_tab_color(self, color):
        """
        Set the color of the worksheet tab.

        Args:
            color: A #RGB color index.

        Returns:
            Nothing.

        """
        self.tab_color = xl_color(color)

    def protect(self, password='', options=None):
        """
        Set the password and protection options of the worksheet.

        Args:
            password: An optional password string.
            options:  A dictionary of worksheet objects to protect.

        Returns:
            Nothing.

        """
        if password != '':
            password = self._encode_password(password)

        if not options:
            options = {}

        # Default values for objects that can be protected.
        defaults = {
            'sheet': True,
            'content': False,
            'objects': False,
            'scenarios': False,
            'format_cells': False,
            'format_columns': False,
            'format_rows': False,
            'insert_columns': False,
            'insert_rows': False,
            'insert_hyperlinks': False,
            'delete_columns': False,
            'delete_rows': False,
            'select_locked_cells': True,
            'sort': False,
            'autofilter': False,
            'pivot_tables': False,
            'select_unlocked_cells': True}

        # Overwrite the defaults with user specified values.
        for key in (options.keys()):

            if key in defaults:
                defaults[key] = options[key]
            else:
                warn("Unknown protection object: '%s'" % key)

        # Set the password after the user defined values.
        defaults['password'] = password

        self.protect_options = defaults

    @convert_cell_args
    def insert_button(self, row, col, options=None):
        """
        Insert a button form object into the worksheet.

        Args:
            row:     The cell row (zero indexed).
            col:     The cell column (zero indexed).
            options: Button formatting options.

        Returns:
            0:  Success.
            -1: Row or column is out of worksheet bounds.

        """
        # Check insert (row, col) without storing.
        if self._check_dimensions(row, col, True, True):
            warn('Cannot insert button at (%d, %d).' % (row, col))
            return -1

        if options is None:
            options = {}

        button = self._button_params(row, col, options)

        self.buttons_list.append(button)

        self.has_vml = 1

    ###########################################################################
    #
    # Public API. Page Setup methods.
    #
    ###########################################################################
    def set_landscape(self):
        """
        Set the page orientation as landscape.

        Args:
            None.

        Returns:
            Nothing.

        """
        self.orientation = 0
        self.page_setup_changed = True

    def set_portrait(self):
        """
        Set the page orientation as portrait.

        Args:
            None.

        Returns:
            Nothing.

        """
        self.orientation = 1
        self.page_setup_changed = True

    def set_page_view(self):
        """
        Set the page view mode.

        Args:
            None.

        Returns:
            Nothing.

        """
        self.page_view = 1

    def set_paper(self, paper_size):
        """
        Set the paper type. US Letter = 1, A4 = 9.

        Args:
            paper_size: Paper index.

        Returns:
            Nothing.

        """
        if paper_size:
            self.paper_size = paper_size
            self.page_setup_changed = True

    def center_horizontally(self):
        """
        Center the page horizontally.

        Args:
            None.

        Returns:
            Nothing.

        """
        self.print_options_changed = True
        self.hcenter = 1

    def center_vertically(self):
        """
        Center the page vertically.

        Args:
            None.

        Returns:
            Nothing.

        """
        self.print_options_changed = True
        self.vcenter = 1

    def set_margins(self, left=0.7, right=0.7, top=0.75, bottom=0.75):
        """
        Set all the page margins in inches.

        Args:
            left:   Left margin.
            right:  Right margin.
            top:    Top margin.
            bottom: Bottom margin.

        Returns:
            Nothing.

        """
        self.margin_left = left
        self.margin_right = right
        self.margin_top = top
        self.margin_bottom = bottom

    def set_header(self, header='', options=None, margin=None):
        """
        Set the page header caption and optional margin.

        Args:
            header:  Header string.
            margin:  Header margin.
            options: Header options, mainly for images.

        Returns:
            Nothing.

        """
        header_orig = header
        header = header.replace('&[Picture]', '&G')

        if len(header) >= 255:
            warn('Header string must be less than 255 characters')
            return

        if options is not None:
            # For backward compatibility allow options to be the margin.
            if not isinstance(options, dict):
                options = {'margin': options}
        else:
            options = {}

        # Copy the user defined options so they aren't modified.
        options = options.copy()

        # For backward compatibility.
        if margin is not None:
            options['margin'] = margin

        # Reset the list in case the function is called more than once.
        self.header_images = []

        if options.get('image_left'):
            self.header_images.append([options.get('image_left'),
                                       options.get('image_data_left'),
                                       'LH'])

        if options.get('image_center'):
            self.header_images.append([options.get('image_center'),
                                       options.get('image_data_center'),
                                       'CH'])

        if options.get('image_right'):
            self.header_images.append([options.get('image_right'),
                                       options.get('image_data_right'),
                                       'RH'])

        placeholder_count = header.count('&G')
        image_count = len(self.header_images)

        if placeholder_count != image_count:
            warn("Number of header images (%s) doesn't match placeholder "
                 "count (%s) in string: %s"
                 % (image_count, placeholder_count, header_orig))
            self.header_images = []
            return

        if 'align_with_margins' in options:
            self.header_footer_aligns = options['align_with_margins']

        if 'scale_with_doc' in options:
            self.header_footer_scales = options['scale_with_doc']

        self.header = header
        self.margin_header = options.get('margin', 0.3)
        self.header_footer_changed = True

        if image_count:
            self.has_header_vml = True

    def set_footer(self, footer='', options=None, margin=None):
        """
        Set the page footer caption and optional margin.

        Args:
            footer:  Footer string.
            margin:  Footer margin.
            options: Footer options, mainly for images.

        Returns:
            Nothing.

        """
        footer_orig = footer
        footer = footer.replace('&[Picture]', '&G')

        if len(footer) >= 255:
            warn('Footer string must be less than 255 characters')
            return

        if options is not None:
            # For backward compatibility allow options to be the margin.
            if not isinstance(options, dict):
                options = {'margin': options}
        else:
            options = {}

        # Copy the user defined options so they aren't modified.
        options = options.copy()

        # For backward compatibility.
        if margin is not None:
            options['margin'] = margin

        # Reset the list in case the function is called more than once.
        self.footer_images = []

        if options.get('image_left'):
            self.footer_images.append([options.get('image_left'),
                                       options.get('image_data_left'),
                                       'LF'])

        if options.get('image_center'):
            self.footer_images.append([options.get('image_center'),
                                       options.get('image_data_center'),
                                       'CF'])

        if options.get('image_right'):
            self.footer_images.append([options.get('image_right'),
                                       options.get('image_data_right'),
                                       'RF'])

        placeholder_count = footer.count('&G')
        image_count = len(self.footer_images)

        if placeholder_count != image_count:
            warn("Number of footer images (%s) doesn't match placeholder "
                 "count (%s) in string: %s"
                 % (image_count, placeholder_count, footer_orig))
            self.footer_images = []
            return

        if 'align_with_margins' in options:
            self.header_footer_aligns = options['align_with_margins']

        if 'scale_with_doc' in options:
            self.header_footer_scales = options['scale_with_doc']

        self.footer = footer
        self.margin_footer = options.get('margin', 0.3)
        self.header_footer_changed = True

        if image_count:
            self.has_header_vml = True

    def repeat_rows(self, first_row, last_row=None):
        """
        Set the rows to repeat at the top of each printed page.

        Args:
            first_row: Start row for range.
            last_row: End row for range.

        Returns:
            Nothing.

        """
        if last_row is None:
            last_row = first_row

        # Convert rows to 1 based.
        first_row += 1
        last_row += 1

        # Create the row range area like: $1:$2.
        area = '$%d:$%d' % (first_row, last_row)

        # Build up the print titles area "Sheet1!$1:$2"
        sheetname = quote_sheetname(self.name)
        self.repeat_row_range = sheetname + '!' + area

    @convert_column_args
    def repeat_columns(self, first_col, last_col=None):
        """
        Set the columns to repeat at the left hand side of each printed page.

        Args:
            first_col: Start column for range.
            last_col: End column for range.

        Returns:
            Nothing.

        """
        if last_col is None:
            last_col = first_col

        # Convert to A notation.
        first_col = xl_col_to_name(first_col, 1)
        last_col = xl_col_to_name(last_col, 1)

        # Create a column range like $C:$D.
        area = first_col + ':' + last_col

        # Build up the print area range "=Sheet2!$C:$D"
        sheetname = quote_sheetname(self.name)
        self.repeat_col_range = sheetname + "!" + area

    def hide_gridlines(self, option=1):
        """
        Set the option to hide gridlines on the screen and the printed page.

        Args:
            option:    0 : Don't hide gridlines
                       1 : Hide printed gridlines only
                       2 : Hide screen and printed gridlines

        Returns:
            Nothing.

        """
        if option == 0:
            self.print_gridlines = 1
            self.screen_gridlines = 1
            self.print_options_changed = True
        elif option == 1:
            self.print_gridlines = 0
            self.screen_gridlines = 1
        else:
            self.print_gridlines = 0
            self.screen_gridlines = 0

    def print_row_col_headers(self):
        """
        Set the option to print the row and column headers on the printed page.

        Args:
            None.

        Returns:
            Nothing.

        """
        self.print_headers = True
        self.print_options_changed = True

    def hide_row_col_headers(self):
        """
        Set the option to hide the row and column headers on the worksheet.

        Args:
            None.

        Returns:
            Nothing.

        """
        self.row_col_headers = True

    @convert_range_args
    def print_area(self, first_row, first_col, last_row, last_col):
        """
        Set the print area in the current worksheet.

        Args:
            first_row:    The first row of the cell range. (zero indexed).
            first_col:    The first column of the cell range.
            last_row:     The last row of the cell range. (zero indexed).
            last_col:     The last column of the cell range.

        Returns:
            0:  Success.
            -1: Row or column is out of worksheet bounds.

        """
        # Set the print area in the current worksheet.

        # Ignore max print area since it is the same as no  area for Excel.
        if (first_row == 0 and first_col == 0
                and last_row == self.xls_rowmax - 1
                and last_col == self.xls_colmax - 1):
            return

        # Build up the print area range "Sheet1!$A$1:$C$13".
        area = self._convert_name_area(first_row, first_col,
                                       last_row, last_col)
        self.print_area_range = area

    def print_across(self):
        """
        Set the order in which pages are printed.

        Args:
            None.

        Returns:
            Nothing.

        """
        self.page_order = 1
        self.page_setup_changed = True

    def fit_to_pages(self, width, height):
        """
        Fit the printed area to a specific number of pages both vertically and
        horizontally.

        Args:
            width:  Number of pages horizontally.
            height: Number of pages vertically.

        Returns:
            Nothing.

        """
        self.fit_page = 1
        self.fit_width = width
        self.fit_height = height
        self.page_setup_changed = True

    def set_start_page(self, start_page):
        """
        Set the start page number when printing.

        Args:
            start_page: Start page number.

        Returns:
            Nothing.

        """
        self.page_start = start_page

    def set_print_scale(self, scale):
        """
        Set the scale factor for the printed page.

        Args:
            scale: Print scale. 10 <= scale <= 400.

        Returns:
            Nothing.

        """
        # Confine the scale to Excel's range.
        if scale < 10 or scale > 400:
            warn("Print scale '%d' outside range: 10 <= scale <= 400" % scale)
            return

        # Turn off "fit to page" option when print scale is on.
        self.fit_page = 0

        self.print_scale = int(scale)
        self.page_setup_changed = True

    def set_h_pagebreaks(self, breaks):
        """
        Set the horizontal page breaks on a worksheet.

        Args:
            breaks: List of rows where the page breaks should be added.

        Returns:
            Nothing.

        """
        self.hbreaks = breaks

    def set_v_pagebreaks(self, breaks):
        """
        Set the horizontal page breaks on a worksheet.

        Args:
            breaks: List of columns where the page breaks should be added.

        Returns:
            Nothing.

        """
        self.vbreaks = breaks

    def set_vba_name(self, name=None):
        """
        Set the VBA name for the worksheet. By default this is the
        same as the sheet name: i.e., Sheet1 etc.

        Args:
            name: The VBA name for the worksheet.

        Returns:
            Nothing.

        """
        if name is not None:
            self.vba_codename = name
        else:
            self.vba_codename = self.name

    ###########################################################################
    #
    # Private API.
    #
    ###########################################################################
    def _initialize(self, init_data):
        self.name = init_data['name']
        self.index = init_data['index']
        self.str_table = init_data['str_table']
        self.worksheet_meta = init_data['worksheet_meta']
        self.constant_memory = init_data['constant_memory']
        self.tmpdir = init_data['tmpdir']
        self.date_1904 = init_data['date_1904']
        self.strings_to_numbers = init_data['strings_to_numbers']
        self.strings_to_formulas = init_data['strings_to_formulas']
        self.strings_to_urls = init_data['strings_to_urls']
        self.nan_inf_to_errors = init_data['nan_inf_to_errors']
        self.default_date_format = init_data['default_date_format']
        self.default_url_format = init_data['default_url_format']
        self.excel2003_style = init_data['excel2003_style']
        self.remove_timezone = init_data['remove_timezone']

        if self.excel2003_style:
            self.original_row_height = 12.75
            self.default_row_height = 12.75
            self.default_row_pixels = 17
            self.margin_left = 0.75
            self.margin_right = 0.75
            self.margin_top = 1
            self.margin_bottom = 1
            self.margin_header = 0.5
            self.margin_footer = 0.5
            self.header_footer_aligns = False

        # Open a temp filehandle to store row data in constant_memory mode.
        if self.constant_memory:
            # This is sub-optimal but we need to create a temp file
            # with utf8 encoding in Python < 3.
            (fd, filename) = tempfile.mkstemp(dir=self.tmpdir)
            os.close(fd)
            self.row_data_filename = filename
            self.row_data_fh = codecs.open(filename, 'w+', 'utf-8')

            # Set as the worksheet filehandle until the file is assembled.
            self.fh = self.row_data_fh

    def _assemble_xml_file(self):
        # Assemble and write the XML file.

        # Write the XML declaration.
        self._xml_declaration()

        # Write the root worksheet element.
        self._write_worksheet()

        # Write the worksheet properties.
        self._write_sheet_pr()

        # Write the worksheet dimensions.
        self._write_dimension()

        # Write the sheet view properties.
        self._write_sheet_views()

        # Write the sheet format properties.
        self._write_sheet_format_pr()

        # Write the sheet column info.
        self._write_cols()

        # Write the worksheet data such as rows columns and cells.
        if not self.constant_memory:
            self._write_sheet_data()
        else:
            self._write_optimized_sheet_data()

        # Write the sheetProtection element.
        self._write_sheet_protection()

        # Write the phoneticPr element.
        if self.excel2003_style:
            self._write_phonetic_pr()

        # Write the autoFilter element.
        self._write_auto_filter()

        # Write the mergeCells element.
        self._write_merge_cells()

        # Write the conditional formats.
        self._write_conditional_formats()

        # Write the dataValidations element.
        self._write_data_validations()

        # Write the hyperlink element.
        self._write_hyperlinks()

        # Write the printOptions element.
        self._write_print_options()

        # Write the worksheet page_margins.
        self._write_page_margins()

        # Write the worksheet page setup.
        self._write_page_setup()

        # Write the headerFooter element.
        self._write_header_footer()

        # Write the rowBreaks element.
        self._write_row_breaks()

        # Write the colBreaks element.
        self._write_col_breaks()

        # Write the drawing element.
        self._write_drawings()

        # Write the legacyDrawing element.
        self._write_legacy_drawing()

        # Write the legacyDrawingHF element.
        self._write_legacy_drawing_hf()

        # Write the tableParts element.
        self._write_table_parts()

        # Write the extLst elements.
        self._write_ext_list()

        # Close the worksheet tag.
        self._xml_end_tag('worksheet')

        # Close the file.
        self._xml_close()

    def _check_dimensions(self, row, col, ignore_row=False, ignore_col=False):
        # Check that row and col are valid and store the max and min
        # values for use in other methods/elements. The ignore_row /
        # ignore_col flags is used to indicate that we wish to perform
        # the dimension check without storing the value. The ignore
        # flags are use by set_row() and data_validate.

        # Check that the row/col are within the worksheet bounds.
        if row < 0 or col < 0:
            return -1
        if row >= self.xls_rowmax or col >= self.xls_colmax:
            return -1

        # In constant_memory mode we don't change dimensions for rows
        # that are already written.
        if not ignore_row and not ignore_col and self.constant_memory:
            if row < self.previous_row:
                return -2

        if not ignore_row:
            if self.dim_rowmin is None or row < self.dim_rowmin:
                self.dim_rowmin = row
            if self.dim_rowmax is None or row > self.dim_rowmax:
                self.dim_rowmax = row

        if not ignore_col:
            if self.dim_colmin is None or col < self.dim_colmin:
                self.dim_colmin = col
            if self.dim_colmax is None or col > self.dim_colmax:
                self.dim_colmax = col

        return 0

    def _convert_date_time(self, dt_obj):
        # Convert a datetime object to an Excel serial date and time.
        return datetime_to_excel_datetime(dt_obj,
                                          self.date_1904,
                                          self.remove_timezone)

    def _convert_name_area(self, row_num_1, col_num_1, row_num_2, col_num_2):
        # Convert zero indexed rows and columns to the format required by
        # worksheet named ranges, eg, "Sheet1!$A$1:$C$13".

        range1 = ''
        range2 = ''
        area = ''
        row_col_only = 0

        # Convert to A1 notation.
        col_char_1 = xl_col_to_name(col_num_1, 1)
        col_char_2 = xl_col_to_name(col_num_2, 1)
        row_char_1 = '$' + str(row_num_1 + 1)
        row_char_2 = '$' + str(row_num_2 + 1)

        # We need to handle special cases that refer to rows or columns only.
        if row_num_1 == 0 and row_num_2 == self.xls_rowmax - 1:
            range1 = col_char_1
            range2 = col_char_2
            row_col_only = 1
        elif col_num_1 == 0 and col_num_2 == self.xls_colmax - 1:
            range1 = row_char_1
            range2 = row_char_2
            row_col_only = 1
        else:
            range1 = col_char_1 + row_char_1
            range2 = col_char_2 + row_char_2

        # A repeated range is only written once (if it isn't a special case).
        if range1 == range2 and not row_col_only:
            area = range1
        else:
            area = range1 + ':' + range2

        # Build up the print area range "Sheet1!$A$1:$C$13".
        sheetname = quote_sheetname(self.name)
        area = sheetname + "!" + area

        return area

    def _sort_pagebreaks(self, breaks):
        # This is an internal method used to filter elements of a list of
        # pagebreaks used in the _store_hbreak() and _store_vbreak() methods.
        # It:
        #   1. Removes duplicate entries from the list.
        #   2. Sorts the list.
        #   3. Removes 0 from the list if present.
        if not breaks:
            return

        breaks_set = set(breaks)

        if 0 in breaks_set:
            breaks_set.remove(0)

        breaks_list = list(breaks_set)
        breaks_list.sort()

        # The Excel 2007 specification says that the maximum number of page
        # breaks is 1026. However, in practice it is actually 1023.
        max_num_breaks = 1023
        if len(breaks_list) > max_num_breaks:
            breaks_list = breaks_list[:max_num_breaks]

        return breaks_list

    def _extract_filter_tokens(self, expression):
        # Extract the tokens from the filter expression. The tokens are mainly
        # non-whitespace groups. The only tricky part is to extract string
        # tokens that contain whitespace and/or quoted double quotes (Excel's
        # escaped quotes).
        #
        # Examples: 'x <  2000'
        #           'x >  2000 and x <  5000'
        #           'x = "foo"'
        #           'x = "foo bar"'
        #           'x = "foo "" bar"'
        #
        if not expression:
            return []

        token_re = re.compile(r'"(?:[^"]|"")*"|\S+')
        tokens = token_re.findall(expression)

        new_tokens = []
        # Remove single leading and trailing quotes and un-escape other quotes.
        for token in tokens:
            if token.startswith('"'):
                token = token[1:]

            if token.endswith('"'):
                token = token[:-1]

            token = token.replace('""', '"')

            new_tokens.append(token)

        return new_tokens

    def _parse_filter_expression(self, expression, tokens):
        # Converts the tokens of a possibly conditional expression into 1 or 2
        # sub expressions for further parsing.
        #
        # Examples:
        #          ('x', '==', 2000) -> exp1
        #          ('x', '>',  2000, 'and', 'x', '<', 5000) -> exp1 and exp2

        if len(tokens) == 7:
            # The number of tokens will be either 3 (for 1 expression)
            # or 7 (for 2  expressions).
            conditional = tokens[3]

            if re.match('(and|&&)', conditional):
                conditional = 0
            elif re.match(r'(or|\|\|)', conditional):
                conditional = 1
            else:
                warn("Token '%s' is not a valid conditional "
                     "in filter expression '%s'" % (conditional, expression))

            expression_1 = self._parse_filter_tokens(expression, tokens[0:3])
            expression_2 = self._parse_filter_tokens(expression, tokens[4:7])

            return expression_1 + [conditional] + expression_2
        else:
            return self._parse_filter_tokens(expression, tokens)

    def _parse_filter_tokens(self, expression, tokens):
        # Parse the 3 tokens of a filter expression and return the operator
        # and token. The use of numbers instead of operators is a legacy of
        # Spreadsheet::WriteExcel.
        operators = {
            '==': 2,
            '=': 2,
            '=~': 2,
            'eq': 2,

            '!=': 5,
            '!~': 5,
            'ne': 5,
            '<>': 5,

            '<': 1,
            '<=': 3,
            '>': 4,
            '>=': 6,
        }

        operator = operators.get(tokens[1], None)
        token = tokens[2]

        # Special handling of "Top" filter expressions.
        if re.match('top|bottom', tokens[0].lower()):
            value = int(tokens[1])

            if value < 1 or value > 500:
                warn("The value '%d' in expression '%s' "
                     "must be in the range 1 to 500" % (value, expression))

            token = token.lower()

            if token != 'items' and token != '%':
                warn("The type '%s' in expression '%s' "
                     "must be either 'items' or '%'" % (token, expression))

            if tokens[0].lower() == 'top':
                operator = 30
            else:
                operator = 32

            if tokens[2] == '%':
                operator += 1

            token = str(value)

        if not operator and tokens[0]:
            warn("Token '%s' is not a valid operator "
                 "in filter expression '%s'" % (token[0], expression))

        # Special handling for Blanks/NonBlanks.
        if re.match('blanks|nonblanks', token.lower()):
            # Only allow Equals or NotEqual in this context.
            if operator != 2 and operator != 5:
                warn("The operator '%s' in expression '%s' "
                     "is not valid in relation to Blanks/NonBlanks'"
                     % (tokens[1], expression))

            token = token.lower()

            # The operator should always be 2 (=) to flag a "simple" equality
            # in the binary record. Therefore we convert <> to =.
            if token == 'blanks':
                if operator == 5:
                    token = ' '
            else:
                if operator == 5:
                    operator = 2
                    token = 'blanks'
                else:
                    operator = 5
                    token = ' '

        # if the string token contains an Excel match character then change the
        # operator type to indicate a non "simple" equality.
        if operator == 2 and re.search('[*?]', token):
            operator = 22

        return [operator, token]

    def _encode_password(self, plaintext):
        # Encode the worksheet protection "password" as a simple hash.
        # Based on the algorithm by Daniel Rentz of OpenOffice.
        i = 0
        count = len(plaintext)
        digits = []

        for char in plaintext:
            i += 1
            char = ord(char) << i
            low_15 = char & 0x7fff
            high_15 = char & 0x7fff << 15
            high_15 >>= 15
            char = low_15 | high_15
            digits.append(char)

        password_hash = 0x0000

        for digit in digits:
            password_hash ^= digit

        password_hash ^= count
        password_hash ^= 0xCE4B

        return "%X" % password_hash

    def _prepare_image(self, index, image_id, drawing_id, width, height,
                       name, image_type, x_dpi, y_dpi):
        # Set up images/drawings.
        drawing_type = 2
        (row, col, _, x_offset, y_offset,
            x_scale, y_scale, url, tip, anchor, _) = self.images[index]

        width *= x_scale
        height *= y_scale

        # Scale by non 96dpi resolutions.
        width *= 96.0 / x_dpi
        height *= 96.0 / y_dpi

        dimensions = self._position_object_emus(col, row, x_offset, y_offset,
                                                width, height)
        # Convert from pixels to emus.
        width = int(0.5 + (width * 9525))
        height = int(0.5 + (height * 9525))

        # Create a Drawing obj to use with worksheet unless one already exists.
        if not self.drawing:
            drawing = Drawing()
            drawing.embedded = 1
            self.drawing = drawing

            self.external_drawing_links.append(['/drawing',
                                                '../drawings/drawing'
                                                + str(drawing_id)
                                                + '.xml', None])
        else:
            drawing = self.drawing

        drawing_object = [drawing_type]
        drawing_object.extend(dimensions)
        drawing_object.extend([width, height, name, None, url, tip, anchor])

        drawing._add_drawing_object(drawing_object)

        if url:
            rel_type = "/hyperlink"
            target_mode = "External"

            if re.match('(ftp|http)s?://', url):
                target = url

            if re.match('external:', url):
                target = url.replace('external:', '')

            if re.match("internal:", url):
                target = url.replace('internal:', '#')
                target_mode = None

            self.drawing_links.append([rel_type, target, target_mode])

        self.drawing_links.append(['/image',
                                   '../media/image'
                                   + str(image_id) + '.'
                                   + image_type])

    def _prepare_shape(self, index, drawing_id):
        # Set up shapes/drawings.
        drawing_type = 3

        (row, col, x_offset, y_offset,
            x_scale, y_scale, text, options) = self.shapes[index]

        width = options.get('width', self.default_col_pixels * 3)
        height = options.get('height', self.default_row_pixels * 6)

        width *= x_scale
        height *= y_scale

        dimensions = self._position_object_emus(col, row, x_offset, y_offset,
                                                width, height)

        # Convert from pixels to emus.
        width = int(0.5 + (width * 9525))
        height = int(0.5 + (height * 9525))

        # Create a Drawing obj to use with worksheet unless one already exists.
        if not self.drawing:
            drawing = Drawing()
            drawing.embedded = 1
            self.drawing = drawing

            self.external_drawing_links.append(['/drawing',
                                                '../drawings/drawing'
                                                + str(drawing_id)
                                                + '.xml', None])
        else:
            drawing = self.drawing

        shape = Shape('rect', 'TextBox', options)
        shape.text = text

        drawing_object = [drawing_type]
        drawing_object.extend(dimensions)
        drawing_object.extend([width, height, None, shape, None,
                               None, None])

        drawing._add_drawing_object(drawing_object)

    def _prepare_header_image(self, image_id, width, height, name, image_type,
                              position, x_dpi, y_dpi):
        # Set up an image without a drawing object for header/footer images.

        # Strip the extension from the filename.
        name = re.sub(r'\..*$', '', name)

        self.header_images_list.append([width, height, name, position,
                                        x_dpi, y_dpi])

        self.vml_drawing_links.append(['/image',
                                       '../media/image'
                                       + str(image_id) + '.'
                                       + image_type])

    def _prepare_chart(self, index, chart_id, drawing_id):
        # Set up chart/drawings.
        drawing_type = 1

        (row, col, chart, x_offset, y_offset, x_scale, y_scale) = \
            self.charts[index]

        chart.id = chart_id - 1

        # Use user specified dimensions, if any.
        width = int(0.5 + (chart.width * x_scale))
        height = int(0.5 + (chart.height * y_scale))

        dimensions = self._position_object_emus(col, row, x_offset, y_offset,
                                                width, height)

        # Set the chart name for the embedded object if it has been specified.
        name = chart.chart_name

        # Create a Drawing obj to use with worksheet unless one already exists.
        if not self.drawing:
            drawing = Drawing()
            drawing.embedded = 1
            self.drawing = drawing

            self.external_drawing_links.append(['/drawing',
                                                '../drawings/drawing'
                                                + str(drawing_id)
                                                + '.xml'])
        else:
            drawing = self.drawing

        drawing_object = [drawing_type]
        drawing_object.extend(dimensions)
        drawing_object.extend([width, height, name, None])

        drawing._add_drawing_object(drawing_object)

        self.drawing_links.append(['/chart',
                                   '../charts/chart'
                                   + str(chart_id)
                                   + '.xml'])

    def _position_object_emus(self, col_start, row_start, x1, y1,
                              width, height):
        # Calculate the vertices that define the position of a graphical
        # object within the worksheet in EMUs.
        #
        # The vertices are expressed as English Metric Units (EMUs). There are
        # 12,700 EMUs per point. Therefore, 12,700 * 3 /4 = 9,525 EMUs per
        # pixel
        (col_start, row_start, x1, y1,
         col_end, row_end, x2, y2, x_abs, y_abs) = \
            self._position_object_pixels(col_start, row_start, x1, y1,
                                         width, height)

        # Convert the pixel values to EMUs. See above.
        x1 = int(0.5 + 9525 * x1)
        y1 = int(0.5 + 9525 * y1)
        x2 = int(0.5 + 9525 * x2)
        y2 = int(0.5 + 9525 * y2)
        x_abs = int(0.5 + 9525 * x_abs)
        y_abs = int(0.5 + 9525 * y_abs)

        return (col_start, row_start, x1, y1, col_end, row_end, x2, y2,
                x_abs, y_abs)

    # Calculate the vertices that define the position of a graphical object
    # within the worksheet in pixels.
    #
    #         +------------+------------+
    #         |     A      |      B     |
    #   +-----+------------+------------+
    #   |     |(x1,y1)     |            |
    #   |  1  |(A1)._______|______      |
    #   |     |    |              |     |
    #   |     |    |              |     |
    #   +-----+----|    OBJECT    |-----+
    #   |     |    |              |     |
    #   |  2  |    |______________.     |
    #   |     |            |        (B2)|
    #   |     |            |     (x2,y2)|
    #   +---- +------------+------------+
    #
    # Example of an object that covers some of the area from cell A1 to  B2.
    #
    # Based on the width and height of the object we need to calculate 8 vars:
    #
    #     col_start, row_start, col_end, row_end, x1, y1, x2, y2.
    #
    # We also calculate the absolute x and y position of the top left vertex of
    # the object. This is required for images.
    #
    # The width and height of the cells that the object occupies can be
    # variable and have to be taken into account.
    #
    # The values of col_start and row_start are passed in from the calling
    # function. The values of col_end and row_end are calculated by
    # subtracting the width and height of the object from the width and
    # height of the underlying cells.
    #
    def _position_object_pixels(self, col_start, row_start, x1, y1,
                                width, height):
        # col_start       # Col containing upper left corner of object.
        # x1              # Distance to left side of object.
        #
        # row_start       # Row containing top left corner of object.
        # y1              # Distance to top of object.
        #
        # col_end         # Col containing lower right corner of object.
        # x2              # Distance to right side of object.
        #
        # row_end         # Row containing bottom right corner of object.
        # y2              # Distance to bottom of object.
        #
        # width           # Width of object frame.
        # height          # Height of object frame.
        #
        # x_abs           # Absolute distance to left side of object.
        # y_abs           # Absolute distance to top side of object.
        x_abs = 0
        y_abs = 0

        # Adjust start column for negative offsets.
        while x1 < 0 and col_start > 0:
            x1 += self._size_col(col_start - 1)
            col_start -= 1

        # Adjust start row for negative offsets.
        while y1 < 0 and row_start > 0:
            y1 += self._size_row(row_start - 1)
            row_start -= 1

        # Ensure that the image isn't shifted off the page at top left.
        if x1 < 0:
            x1 = 0

        if y1 < 0:
            y1 = 0

        # Calculate the absolute x offset of the top-left vertex.
        if self.col_size_changed:
            for col_id in range(col_start):
                x_abs += self._size_col(col_id)
        else:
            # Optimization for when the column widths haven't changed.
            x_abs += self.default_col_pixels * col_start

        x_abs += x1

        # Calculate the absolute y offset of the top-left vertex.
        if self.row_size_changed:
            for row_id in range(row_start):
                y_abs += self._size_row(row_id)
        else:
            # Optimization for when the row heights haven't changed.
            y_abs += self.default_row_pixels * row_start

        y_abs += y1

        # Adjust start column for offsets that are greater than the col width.
        while x1 >= self._size_col(col_start):
            x1 -= self._size_col(col_start)
            col_start += 1

        # Adjust start row for offsets that are greater than the row height.
        while y1 >= self._size_row(row_start):
            y1 -= self._size_row(row_start)
            row_start += 1

        # Initialize end cell to the same as the start cell.
        col_end = col_start
        row_end = row_start

        width = width + x1
        height = height + y1

        # Subtract the underlying cell widths to find end cell of the object.
        while width >= self._size_col(col_end):
            width -= self._size_col(col_end)
            col_end += 1

        # Subtract the underlying cell heights to find end cell of the object.

        while height >= self._size_row(row_end):
            height -= self._size_row(row_end)
            row_end += 1

        # The end vertices are whatever is left from the width and height.
        x2 = width
        y2 = height

        return ([col_start, row_start, x1, y1, col_end, row_end, x2, y2,
                x_abs, y_abs])

    def _size_col(self, col):
        # Convert the width of a cell from user's units to pixels. Excel rounds
        # the column width to the nearest pixel. If the width hasn't been set
        # by the user we use the default value. If the column is hidden it
        # has a value of zero.
        max_digit_width = 7  # For Calabri 11.
        padding = 5
        pixels = 0

        # Look up the cell value to see if it has been changed.
        if col in self.col_sizes and self.col_sizes[col] is not None:
            width = self.col_sizes[col]

            # Convert to pixels.
            if width == 0:
                pixels = 0
            elif width < 1:
                pixels = int(width * (max_digit_width + padding) + 0.5)
            else:
                pixels = int(width * max_digit_width + 0.5) + padding
        else:
            pixels = self.default_col_pixels

        return pixels

    def _size_row(self, row):
        # Convert the height of a cell from user's units to pixels. If the
        # height hasn't been set by the user we use the default value. If
        #  the row is hidden it has a value of zero.
        pixels = 0

        # Look up the cell value to see if it has been changed
        if row in self.row_sizes:
            height = self.row_sizes[row]

            if height == 0:
                pixels = 0
            else:
                pixels = int(4.0 / 3.0 * height)
        else:
            pixels = int(4.0 / 3.0 * self.default_row_height)

        return pixels

    def _comment_params(self, row, col, string, options):
        # This method handles the additional optional parameters to
        # write_comment() as well as calculating the comment object
        # position and vertices.
        default_width = 128
        default_height = 74

        params = {
            'author': None,
            'color': '#ffffe1',
            'start_cell': None,
            'start_col': None,
            'start_row': None,
            'visible': None,
            'width': default_width,
            'height': default_height,
            'x_offset': None,
            'x_scale': 1,
            'y_offset': None,
            'y_scale': 1,
            'font_name': 'Tahoma',
            'font_size': 8,
            'font_family': 2,
        }

        # Overwrite the defaults with any user supplied values. Incorrect or
        # misspelled parameters are silently ignored.
        for key in options.keys():
            params[key] = options[key]

        # Ensure that a width and height have been set.
        if not params['width']:
            params['width'] = default_width
        if not params['height']:
            params['height'] = default_height

        # Set the comment background color.
        params['color'] = xl_color(params['color']).lower()

        # Convert from Excel XML style color to XML html style color.
        params['color'] = params['color'].replace('ff', '#', 1)

        # Convert a cell reference to a row and column.
        if params['start_cell'] is not None:
            (start_row, start_col) = xl_cell_to_rowcol(params['start_cell'])
            params['start_row'] = start_row
            params['start_col'] = start_col

        # Set the default start cell and offsets for the comment. These are
        # generally fixed in relation to the parent cell. However there are
        # some edge cases for cells at the, er, edges.
        row_max = self.xls_rowmax
        col_max = self.xls_colmax

        if params['start_row'] is None:
            if row == 0:
                params['start_row'] = 0
            elif row == row_max - 3:
                params['start_row'] = row_max - 7
            elif row == row_max - 2:
                params['start_row'] = row_max - 6
            elif row == row_max - 1:
                params['start_row'] = row_max - 5
            else:
                params['start_row'] = row - 1

        if params['y_offset'] is None:
            if row == 0:
                params['y_offset'] = 2
            elif row == row_max - 3:
                params['y_offset'] = 16
            elif row == row_max - 2:
                params['y_offset'] = 16
            elif row == row_max - 1:
                params['y_offset'] = 14
            else:
                params['y_offset'] = 10

        if params['start_col'] is None:
            if col == col_max - 3:
                params['start_col'] = col_max - 6
            elif col == col_max - 2:
                params['start_col'] = col_max - 5
            elif col == col_max - 1:
                params['start_col'] = col_max - 4
            else:
                params['start_col'] = col + 1

        if params['x_offset'] is None:
            if col == col_max - 3:
                params['x_offset'] = 49
            elif col == col_max - 2:
                params['x_offset'] = 49
            elif col == col_max - 1:
                params['x_offset'] = 49
            else:
                params['x_offset'] = 15

        # Scale the size of the comment box if required.
        if params['x_scale']:
            params['width'] = params['width'] * params['x_scale']

        if params['y_scale']:
            params['height'] = params['height'] * params['y_scale']

        # Round the dimensions to the nearest pixel.
        params['width'] = int(0.5 + params['width'])
        params['height'] = int(0.5 + params['height'])

        # Calculate the positions of the comment object.
        vertices = self._position_object_pixels(
            params['start_col'], params['start_row'], params['x_offset'],
            params['y_offset'], params['width'], params['height'])

        # Add the width and height for VML.
        vertices.append(params['width'])
        vertices.append(params['height'])

        return ([row, col, string, params['author'],
                 params['visible'], params['color'],
                 params['font_name'], params['font_size'],
                 params['font_family']] + [vertices])

    def _button_params(self, row, col, options):
        # This method handles the parameters passed to insert_button() as well
        # as calculating the comment object position and vertices.

        default_height = self.default_row_pixels
        default_width = self.default_col_pixels

        button_number = 1 + len(self.buttons_list)
        button = {'row': row, 'col': col, 'font': {}}
        params = {}

        # Overwrite the defaults with any user supplied values. Incorrect or
        # misspelled parameters are silently ignored.
        for key in options.keys():
            params[key] = options[key]

        # Set the button caption.
        caption = params.get('caption')

        # Set a default caption if none was specified by user.
        if caption is None:
            caption = 'Button %d' % button_number

        button['font']['caption'] = caption

        # Set the macro name.
        if params.get('macro'):
            button['macro'] = '[0]!' + params['macro']
        else:
            button['macro'] = '[0]!Button%d_Click' % button_number

        # Ensure that a width and height have been set.
        params['width'] = params.get('width', default_width)
        params['height'] = params.get('height', default_height)

        # Set the x/y offsets.
        params['x_offset'] = params.get('x_offset', 0)
        params['y_offset'] = params.get('y_offset', 0)

        # Scale the size of the button if required.
        params['width'] = params['width'] * params.get('x_scale', 1)
        params['height'] = params['height'] * params.get('y_scale', 1)

        # Round the dimensions to the nearest pixel.
        params['width'] = int(0.5 + params['width'])
        params['height'] = int(0.5 + params['height'])

        params['start_row'] = row
        params['start_col'] = col

        # Calculate the positions of the button object.
        vertices = self._position_object_pixels(
            params['start_col'], params['start_row'], params['x_offset'],
            params['y_offset'], params['width'], params['height'])

        # Add the width and height for VML.
        vertices.append(params['width'])
        vertices.append(params['height'])

        button['vertices'] = vertices

        return button

    def _prepare_vml_objects(self, vml_data_id, vml_shape_id, vml_drawing_id,
                             comment_id):
        comments = []
        # Sort the comments into row/column order for easier comparison
        # testing and set the external links for comments and buttons.
        row_nums = sorted(self.comments.keys())

        for row in row_nums:
            col_nums = sorted(self.comments[row].keys())

            for col in col_nums:
                # Set comment visibility if required and not user defined.
                if self.comments_visible:
                    if self.comments[row][col][4] is None:
                        self.comments[row][col][4] = 1

                # Set comment author if not already user defined.
                if self.comments[row][col][3] is None:
                    self.comments[row][col][3] = self.comments_author

                comments.append(self.comments[row][col])

        self.external_vml_links.append(['/vmlDrawing',
                                        '../drawings/vmlDrawing'
                                        + str(vml_drawing_id)
                                        + '.vml'])

        if self.has_comments:
            self.comments_list = comments

            self.external_comment_links.append(['/comments',
                                                '../comments'
                                                + str(comment_id)
                                                + '.xml'])

        count = len(comments)
        start_data_id = vml_data_id

        # The VML o:idmap data id contains a comma separated range when there
        # is more than one 1024 block of comments, like this: data="1,2".
        for i in range(int(count / 1024)):
            vml_data_id = '%s,%d' % (vml_data_id, start_data_id + i + 1)

        self.vml_data_id = vml_data_id
        self.vml_shape_id = vml_shape_id

        return count

    def _prepare_header_vml_objects(self, vml_header_id, vml_drawing_id):
        # Set up external linkage for VML header/footer images.

        self.vml_header_id = vml_header_id

        self.external_vml_links.append(['/vmlDrawing',
                                        '../drawings/vmlDrawing'
                                        + str(vml_drawing_id) + '.vml'])

    def _prepare_tables(self, table_id, seen):
        # Set the table ids for the worksheet tables.
        for table in self.tables:
            table['id'] = table_id

            if table.get('name') is None:
                # Set a default name.
                table['name'] = 'Table' + str(table_id)

            # Check for duplicate table names.
            name = table['name'].lower()

            if name in seen:
                raise DuplicateTableName(
                    "Duplicate name '%s' used in worksheet.add_table()." %
                    table['name'])
            else:
                seen[name] = True

            # Store the link used for the rels file.
            self.external_table_links.append(['/table',
                                              '../tables/table'
                                              + str(table_id)
                                              + '.xml'])
            table_id += 1

    def _table_function_to_formula(self, function, col_name):
        # Convert a table total function to a worksheet formula.
        formula = ''

        # Escape special characters, as required by Excel.
        col_name = re.sub(r"'", "''", col_name)
        col_name = re.sub(r"#", "'#", col_name)
        col_name = re.sub(r"]", "']", col_name)
        col_name = re.sub(r"\[", "'[", col_name)

        subtotals = {
            'average': 101,
            'countNums': 102,
            'count': 103,
            'max': 104,
            'min': 105,
            'stdDev': 107,
            'sum': 109,
            'var': 110,
        }

        if function in subtotals:
            func_num = subtotals[function]
            formula = "SUBTOTAL(%s,[%s])" % (func_num, col_name)
        else:
            warn("Unsupported function '%s' in add_table()" % function)

        return formula

    def _set_spark_color(self, sparkline, options, user_color):
        # Set the sparkline color.
        if user_color not in options:
            return

        sparkline[user_color] = {'rgb': xl_color(options[user_color])}

    def _get_range_data(self, row_start, col_start, row_end, col_end):
        # Returns a range of data from the worksheet _table to be used in
        # chart cached data. Strings are returned as SST ids and decoded
        # in the workbook. Return None for data that doesn't exist since
        # Excel can chart series with data missing.

        if self.constant_memory:
            return ()

        data = []

        # Iterate through the table data.
        for row_num in range(row_start, row_end + 1):
            # Store None if row doesn't exist.
            if row_num not in self.table:
                data.append(None)
                continue

            for col_num in range(col_start, col_end + 1):

                if col_num in self.table[row_num]:
                    cell = self.table[row_num][col_num]

                    type_cell_name = type(cell).__name__

                    if type_cell_name == 'Number':
                        # Return a number with Excel's precision.
                        data.append("%.16g" % cell.number)

                    elif type_cell_name == 'String':
                        # Return a string from it's shared string index.
                        index = cell.string
                        string = self.str_table._get_shared_string(index)

                        data.append(string)

                    elif (type_cell_name == 'Formula'
                            or type_cell_name == 'ArrayFormula'):
                        # Return the formula value.
                        value = cell.value

                        if value is None:
                            value = 0

                        data.append(value)

                    elif type_cell_name == 'Blank':
                        # Return a empty cell.
                        data.append('')
                else:

                    # Store None if column doesn't exist.
                    data.append(None)

        return data

    def _csv_join(self, *items):
        # Create a csv string for use with data validation formulas and lists.

        # Convert non string types to string.
        items = [str(item) if not isinstance(item, str_types) else item
                 for item in items]

        return ','.join(items)

    def _escape_url(self, url):
        # Don't escape URL if it looks already escaped.
        if re.search('%[0-9a-fA-F]{2}', url):
            return url

        # Can't use url.quote() here because it doesn't match Excel.
        url = url.replace('%', '%25')
        url = url.replace('"', '%22')
        url = url.replace(' ', '%20')
        url = url.replace('<', '%3c')
        url = url.replace('>', '%3e')
        url = url.replace('[', '%5b')
        url = url.replace(']', '%5d')
        url = url.replace('^', '%5e')
        url = url.replace('`', '%60')
        url = url.replace('{', '%7b')
        url = url.replace('}', '%7d')

        return url

    ###########################################################################
    #
    # The following font methods are, more or less, duplicated from the
    # Styles class. Not the cleanest version of reuse but works for now.
    #
    ###########################################################################
    def _write_font(self, xf_format):
        # Write the <font> element.
        xml_writer = self.rstring

        xml_writer._xml_start_tag('rPr')

        # Handle the main font properties.
        if xf_format.bold:
            xml_writer._xml_empty_tag('b')
        if xf_format.italic:
            xml_writer._xml_empty_tag('i')
        if xf_format.font_strikeout:
            xml_writer._xml_empty_tag('strike')
        if xf_format.font_outline:
            xml_writer._xml_empty_tag('outline')
        if xf_format.font_shadow:
            xml_writer._xml_empty_tag('shadow')

        # Handle the underline variants.
        if xf_format.underline:
            self._write_underline(xf_format.underline)

        # Handle super/subscript.
        if xf_format.font_script == 1:
            self._write_vert_align('superscript')
        if xf_format.font_script == 2:
            self._write_vert_align('subscript')

        # Write the font size
        xml_writer._xml_empty_tag('sz', [('val', xf_format.font_size)])

        # Handle colors.
        if xf_format.theme:
            self._write_color('theme', xf_format.theme)
        elif xf_format.color_indexed:
            self._write_color('indexed', xf_format.color_indexed)
        elif xf_format.font_color:
            color = self._get_palette_color(xf_format.font_color)
            self._write_rstring_color('rgb', color)
        else:
            self._write_rstring_color('theme', 1)

        # Write some other font properties related to font families.
        xml_writer._xml_empty_tag('rFont', [('val', xf_format.font_name)])
        xml_writer._xml_empty_tag('family', [('val', xf_format.font_family)])

        if xf_format.font_name == 'Calibri' and not xf_format.hyperlink:
            xml_writer._xml_empty_tag('scheme',
                                      [('val', xf_format.font_scheme)])

        xml_writer._xml_end_tag('rPr')

    def _write_underline(self, underline):
        # Write the underline font element.
        attributes = []

        # Handle the underline variants.
        if underline == 2:
            attributes = [('val', 'double')]
        elif underline == 33:
            attributes = [('val', 'singleAccounting')]
        elif underline == 34:
            attributes = [('val', 'doubleAccounting')]

        self.rstring._xml_empty_tag('u', attributes)

    def _write_vert_align(self, val):
        # Write the <vertAlign> font sub-element.
        attributes = [('val', val)]

        self.rstring._xml_empty_tag('vertAlign', attributes)

    def _write_rstring_color(self, name, value):
        # Write the <color> element.
        attributes = [(name, value)]

        self.rstring._xml_empty_tag('color', attributes)

    def _get_palette_color(self, color):
        # Convert the RGB color.
        if color[0] == '#':
            color = color[1:]

        return "FF" + color.upper()

    def _isnan(self, x):
        # Workaround for lack of math.isnan in Python 2.5/Jython.
        return x != x

    def _isinf(self, x):
        # Workaround for lack of math.isinf in Python 2.5/Jython.
        return (x - x) != 0

    def _opt_close(self):
        # Close the row data filehandle in constant_memory mode.
        if not self.row_data_fh_closed:
            self.row_data_fh.close()
            self.row_data_fh_closed = True

    def _opt_reopen(self):
        # Reopen the row data filehandle in constant_memory mode.
        if self.row_data_fh_closed:
            filename = self.row_data_filename
            self.row_data_fh = codecs.open(filename, 'a+', 'utf-8')
            self.row_data_fh_closed = False
            self.fh = self.row_data_fh

    def _set_icon_props(self, total_icons, user_props=None):
        # Set the sub-properties for icons.
        props = []

        # Set the defaults.
        for _ in range(total_icons):
            props.append({'criteria': False,
                          'value': 0,
                          'type': 'percent'})

        # Set the default icon values based on the number of icons.
        if total_icons == 3:
            props[0]['value'] = 67
            props[1]['value'] = 33

        if total_icons == 4:
            props[0]['value'] = 75
            props[1]['value'] = 50
            props[2]['value'] = 25

        if total_icons == 5:
            props[0]['value'] = 80
            props[1]['value'] = 60
            props[2]['value'] = 40
            props[3]['value'] = 20

        # Overwrite default properties with user defined properties.
        if user_props:

            # Ensure we don't set user properties for lowest icon.
            max_data = len(user_props)
            if max_data >= total_icons:
                max_data = total_icons - 1

            for i in range(max_data):

                # Set the user defined 'value' property.
                if user_props[i].get('value') is not None:
                    props[i]['value'] = user_props[i]['value']

                    # Remove the formula '=' sign if it exists.
                    tmp = props[i]['value']
                    if isinstance(tmp, str_types) and tmp.startswith('='):
                        props[i]['value'] = tmp.lstrip('=')

                # Set the user defined 'type' property.
                if user_props[i].get('type'):
                    valid_types = ('percent',
                                   'percentile',
                                   'number',
                                   'formula')

                    if user_props[i]['type'] not in valid_types:
                        warn("Unknown icon property type '%s' for sub-"
                             "property 'type' in conditional_format()" %
                             user_props[i]['type'])
                    else:
                        props[i]['type'] = user_props[i]['type']

                        if props[i]['type'] is 'number':
                            props[i]['type'] = 'num'

                # Set the user defined 'criteria' property.
                criteria = user_props[i].get('criteria')
                if criteria and criteria == '>':
                    props[i]['criteria'] = True

        return props

    ###########################################################################
    #
    # XML methods.
    #
    ###########################################################################

    def _write_worksheet(self):
        # Write the <worksheet> element. This is the root element.

        schema = 'http://schemas.openxmlformats.org/'
        xmlns = schema + 'spreadsheetml/2006/main'
        xmlns_r = schema + 'officeDocument/2006/relationships'
        xmlns_mc = schema + 'markup-compatibility/2006'
        ms_schema = 'http://schemas.microsoft.com/'
        xmlns_x14ac = ms_schema + 'office/spreadsheetml/2009/9/ac'

        attributes = [
            ('xmlns', xmlns),
            ('xmlns:r', xmlns_r)]

        # Add some extra attributes for Excel 2010. Mainly for sparklines.
        if self.excel_version == 2010:
            attributes.append(('xmlns:mc', xmlns_mc))
            attributes.append(('xmlns:x14ac', xmlns_x14ac))
            attributes.append(('mc:Ignorable', 'x14ac'))

        self._xml_start_tag('worksheet', attributes)

    def _write_dimension(self):
        # Write the <dimension> element. This specifies the range of
        # cells in the worksheet. As a special case, empty
        # spreadsheets use 'A1' as a range.

        if self.dim_rowmin is None and self.dim_colmin is None:
            # If the min dimensions are not defined then no dimensions
            # have been set and we use the default 'A1'.
            ref = 'A1'

        elif self.dim_rowmin is None and self.dim_colmin is not None:
            # If the row dimensions aren't set but the column
            # dimensions are set then they have been changed via
            # set_column().

            if self.dim_colmin == self.dim_colmax:
                # The dimensions are a single cell and not a range.
                ref = xl_rowcol_to_cell(0, self.dim_colmin)
            else:
                # The dimensions are a cell range.
                cell_1 = xl_rowcol_to_cell(0, self.dim_colmin)
                cell_2 = xl_rowcol_to_cell(0, self.dim_colmax)
                ref = cell_1 + ':' + cell_2

        elif (self.dim_rowmin == self.dim_rowmax and
              self.dim_colmin == self.dim_colmax):
            # The dimensions are a single cell and not a range.
            ref = xl_rowcol_to_cell(self.dim_rowmin, self.dim_colmin)
        else:
            # The dimensions are a cell range.
            cell_1 = xl_rowcol_to_cell(self.dim_rowmin, self.dim_colmin)
            cell_2 = xl_rowcol_to_cell(self.dim_rowmax, self.dim_colmax)
            ref = cell_1 + ':' + cell_2

        self._xml_empty_tag('dimension', [('ref', ref)])

    def _write_sheet_views(self):
        # Write the <sheetViews> element.
        self._xml_start_tag('sheetViews')

        # Write the sheetView element.
        self._write_sheet_view()

        self._xml_end_tag('sheetViews')

    def _write_sheet_view(self):
        # Write the <sheetViews> element.
        attributes = []

        # Hide screen gridlines if required.
        if not self.screen_gridlines:
            attributes.append(('showGridLines', 0))

        # Hide screen row/column headers.
        if self.row_col_headers:
            attributes.append(('showRowColHeaders', 0))

        # Hide zeroes in cells.
        if not self.show_zeros:
            attributes.append(('showZeros', 0))

        # Display worksheet right to left for Hebrew, Arabic and others.
        if self.is_right_to_left:
            attributes.append(('rightToLeft', 1))

        # Show that the sheet tab is selected.
        if self.selected:
            attributes.append(('tabSelected', 1))

        # Turn outlines off. Also required in the outlinePr element.
        if not self.outline_on:
            attributes.append(("showOutlineSymbols", 0))

        # Set the page view/layout mode if required.
        if self.page_view:
            attributes.append(('view', 'pageLayout'))

        # Set the zoom level.
        if self.zoom != 100:
            if not self.page_view:
                attributes.append(('zoomScale', self.zoom))
                if self.zoom_scale_normal:
                    attributes.append(('zoomScaleNormal', self.zoom))

        attributes.append(('workbookViewId', 0))

        if self.panes or len(self.selections):
            self._xml_start_tag('sheetView', attributes)
            self._write_panes()
            self._write_selections()
            self._xml_end_tag('sheetView')
        else:
            self._xml_empty_tag('sheetView', attributes)

    def _write_sheet_format_pr(self):
        # Write the <sheetFormatPr> element.
        default_row_height = self.default_row_height
        row_level = self.outline_row_level
        col_level = self.outline_col_level

        attributes = [('defaultRowHeight', default_row_height)]

        if self.default_row_height != self.original_row_height:
            attributes.append(('customHeight', 1))

        if self.default_row_zeroed:
            attributes.append(('zeroHeight', 1))

        if row_level:
            attributes.append(('outlineLevelRow', row_level))
        if col_level:
            attributes.append(('outlineLevelCol', col_level))

        if self.excel_version == 2010:
            attributes.append(('x14ac:dyDescent', '0.25'))

        self._xml_empty_tag('sheetFormatPr', attributes)

    def _write_cols(self):
        # Write the <cols> element and <col> sub elements.

        # Exit unless some column have been formatted.
        if not self.colinfo:
            return

        self._xml_start_tag('cols')

        for col in sorted(self.colinfo.keys()):
            self._write_col_info(self.colinfo[col])

        self._xml_end_tag('cols')

    def _write_col_info(self, col_info):
        # Write the <col> element.

        (col_min, col_max, width, cell_format,
         hidden, level, collapsed) = col_info

        custom_width = 1
        xf_index = 0

        # Get the cell_format index.
        if cell_format:
            xf_index = cell_format._get_xf_index()

        # Set the Excel default column width.
        if width is None:
            if not hidden:
                width = 8.43
                custom_width = 0
            else:
                width = 0
        elif width == 8.43:
            # Width is defined but same as default.
            custom_width = 0

        # Convert column width from user units to character width.
        if width > 0:
            # For Calabri 11.
            max_digit_width = 7
            padding = 5

            if width < 1:
                width = int((int(width * (max_digit_width + padding) + 0.5))
                            / float(max_digit_width) * 256.0) / 256.0
            else:
                width = int((int(width * max_digit_width + 0.5) + padding)
                            / float(max_digit_width) * 256.0) / 256.0

        attributes = [
            ('min', col_min + 1),
            ('max', col_max + 1),
            ('width', "%.16g" % width)]

        if xf_index:
            attributes.append(('style', xf_index))
        if hidden:
            attributes.append(('hidden', '1'))
        if custom_width:
            attributes.append(('customWidth', '1'))
        if level:
            attributes.append(('outlineLevel', level))
        if collapsed:
            attributes.append(('collapsed', '1'))

        self._xml_empty_tag('col', attributes)

    def _write_sheet_data(self):
        # Write the <sheetData> element.

        if self.dim_rowmin is None:
            # If the dimensions aren't defined there is no data to write.
            self._xml_empty_tag('sheetData')
        else:
            self._xml_start_tag('sheetData')
            self._write_rows()
            self._xml_end_tag('sheetData')

    def _write_optimized_sheet_data(self):
        # Write the <sheetData> element when constant_memory is on. In this
        # case we read the data stored in the temp file and rewrite it to the
        # XML sheet file.
        if self.dim_rowmin is None:
            # If the dimensions aren't defined then there is no data to write.
            self._xml_empty_tag('sheetData')
        else:
            self._xml_start_tag('sheetData')

            # Rewind the filehandle that was used for temp row data.
            buff_size = 65536
            self.row_data_fh.seek(0)
            data = self.row_data_fh.read(buff_size)

            while data:
                self.fh.write(data)
                data = self.row_data_fh.read(buff_size)

            self.row_data_fh.close()
            os.unlink(self.row_data_filename)

            self._xml_end_tag('sheetData')

    def _write_page_margins(self):
        # Write the <pageMargins> element.
        attributes = [
            ('left', self.margin_left),
            ('right', self.margin_right),
            ('top', self.margin_top),
            ('bottom', self.margin_bottom),
            ('header', self.margin_header),
            ('footer', self.margin_footer)]

        self._xml_empty_tag('pageMargins', attributes)

    def _write_page_setup(self):
        # Write the <pageSetup> element.
        #
        # The following is an example taken from Excel.
        #
        # <pageSetup
        #     paperSize="9"
        #     scale="110"
        #     fitToWidth="2"
        #     fitToHeight="2"
        #     pageOrder="overThenDown"
        #     orientation="portrait"
        #     blackAndWhite="1"
        #     draft="1"
        #     horizontalDpi="200"
        #     verticalDpi="200"
        #     r:id="rId1"
        # />
        #
        attributes = []

        # Skip this element if no page setup has changed.
        if not self.page_setup_changed:
            return

        # Set paper size.
        if self.paper_size:
            attributes.append(('paperSize', self.paper_size))

        # Set the print_scale.
        if self.print_scale != 100:
            attributes.append(('scale', self.print_scale))

        # Set the "Fit to page" properties.
        if self.fit_page and self.fit_width != 1:
            attributes.append(('fitToWidth', self.fit_width))

        if self.fit_page and self.fit_height != 1:
            attributes.append(('fitToHeight', self.fit_height))

        # Set the page print direction.
        if self.page_order:
            attributes.append(('pageOrder', "overThenDown"))

        # Set start page for printing.
        if self.page_start > 1:
            attributes.append(('firstPageNumber', self.page_start))

        # Set page orientation.
        if self.orientation:
            attributes.append(('orientation', 'portrait'))
        else:
            attributes.append(('orientation', 'landscape'))

        # Set start page for printing.
        if self.page_start != 0:
            attributes.append(('useFirstPageNumber', '1'))

        # Set the DPI. Mainly only for testing.
        if self.is_chartsheet:
            if self.horizontal_dpi:
                attributes.append(('horizontalDpi', self.horizontal_dpi))

            if self.vertical_dpi:
                attributes.append(('verticalDpi', self.vertical_dpi))
        else:
            if self.vertical_dpi:
                attributes.append(('verticalDpi', self.vertical_dpi))

            if self.horizontal_dpi:
                attributes.append(('horizontalDpi', self.horizontal_dpi))

        self._xml_empty_tag('pageSetup', attributes)

    def _write_print_options(self):
        # Write the <printOptions> element.
        attributes = []

        if not self.print_options_changed:
            return

        # Set horizontal centering.
        if self.hcenter:
            attributes.append(('horizontalCentered', 1))

        # Set vertical centering.
        if self.vcenter:
            attributes.append(('verticalCentered', 1))

        # Enable row and column headers.
        if self.print_headers:
            attributes.append(('headings', 1))

        # Set printed gridlines.
        if self.print_gridlines:
            attributes.append(('gridLines', 1))

        self._xml_empty_tag('printOptions', attributes)

    def _write_header_footer(self):
        # Write the <headerFooter> element.
        attributes = []

        if not self.header_footer_scales:
            attributes.append(('scaleWithDoc', 0))

        if not self.header_footer_aligns:
            attributes.append(('alignWithMargins', 0))

        if self.header_footer_changed:
            self._xml_start_tag('headerFooter', attributes)
            if self.header:
                self._write_odd_header()
            if self.footer:
                self._write_odd_footer()
            self._xml_end_tag('headerFooter')
        elif self.excel2003_style:
            self._xml_empty_tag('headerFooter', attributes)

    def _write_odd_header(self):
        # Write the <headerFooter> element.
        self._xml_data_element('oddHeader', self.header)

    def _write_odd_footer(self):
        # Write the <headerFooter> element.
        self._xml_data_element('oddFooter', self.footer)

    def _write_rows(self):
        # Write out the worksheet data as a series of rows and cells.
        self._calculate_spans()

        for row_num in range(self.dim_rowmin, self.dim_rowmax + 1):

            if (row_num in self.set_rows or row_num in self.comments
                    or self.table[row_num]):
                # Only process rows with formatting, cell data and/or comments.

                span_index = int(row_num / 16)

                if span_index in self.row_spans:
                    span = self.row_spans[span_index]
                else:
                    span = None

                if self.table[row_num]:
                    # Write the cells if the row contains data.
                    if row_num not in self.set_rows:
                        self._write_row(row_num, span)
                    else:
                        self._write_row(row_num, span, self.set_rows[row_num])

                    for col_num in range(self.dim_colmin, self.dim_colmax + 1):
                        if col_num in self.table[row_num]:
                            col_ref = self.table[row_num][col_num]
                            self._write_cell(row_num, col_num, col_ref)

                    self._xml_end_tag('row')

                elif row_num in self.comments:
                    # Row with comments in cells.
                    self._write_empty_row(row_num, span,
                                          self.set_rows[row_num])
                else:
                    # Blank row with attributes only.
                    self._write_empty_row(row_num, span,
                                          self.set_rows[row_num])

    def _write_single_row(self, current_row_num=0):
        # Write out the worksheet data as a single row with cells.
        # This method is used when constant_memory is on. A single
        # row is written and the data table is reset. That way only
        # one row of data is kept in memory at any one time. We don't
        # write span data in the optimized case since it is optional.

        # Set the new previous row as the current row.
        row_num = self.previous_row
        self.previous_row = current_row_num

        if (row_num in self.set_rows or row_num in self.comments
                or self.table[row_num]):
            # Only process rows with formatting, cell data and/or comments.

            # No span data in optimized mode.
            span = None

            if self.table[row_num]:
                # Write the cells if the row contains data.
                if row_num not in self.set_rows:
                    self._write_row(row_num, span)
                else:
                    self._write_row(row_num, span, self.set_rows[row_num])

                for col_num in range(self.dim_colmin, self.dim_colmax + 1):
                    if col_num in self.table[row_num]:
                        col_ref = self.table[row_num][col_num]
                        self._write_cell(row_num, col_num, col_ref)

                self._xml_end_tag('row')
            else:
                # Row attributes or comments only.
                self._write_empty_row(row_num, span, self.set_rows[row_num])

        # Reset table.
        self.table.clear()

    def _calculate_spans(self):
        # Calculate the "spans" attribute of the <row> tag. This is an
        # XLSX optimization and isn't strictly required. However, it
        # makes comparing files easier. The span is the same for each
        # block of 16 rows.
        spans = {}
        span_min = None
        span_max = None

        for row_num in range(self.dim_rowmin, self.dim_rowmax + 1):

            if row_num in self.table:
                # Calculate spans for cell data.
                for col_num in range(self.dim_colmin, self.dim_colmax + 1):
                    if col_num in self.table[row_num]:
                        if span_min is None:
                            span_min = col_num
                            span_max = col_num
                        else:
                            if col_num < span_min:
                                span_min = col_num
                            if col_num > span_max:
                                span_max = col_num

            if row_num in self.comments:
                # Calculate spans for comments.
                for col_num in range(self.dim_colmin, self.dim_colmax + 1):
                    if (row_num in self.comments
                            and col_num in self.comments[row_num]):
                        if span_min is None:
                            span_min = col_num
                            span_max = col_num
                        else:
                            if col_num < span_min:
                                span_min = col_num
                            if col_num > span_max:
                                span_max = col_num

            if ((row_num + 1) % 16 == 0) or row_num == self.dim_rowmax:
                span_index = int(row_num / 16)

                if span_min is not None:
                    span_min += 1
                    span_max += 1
                    spans[span_index] = "%s:%s" % (span_min, span_max)
                    span_min = None

        self.row_spans = spans

    def _write_row(self, row, spans, properties=None, empty_row=False):
        # Write the <row> element.
        xf_index = 0

        if properties:
            height, cell_format, hidden, level, collapsed = properties
        else:
            height, cell_format, hidden, level, collapsed = None, None, 0, 0, 0

        if height is None:
            height = self.default_row_height

        attributes = [('r', row + 1)]

        # Get the cell_format index.
        if cell_format:
            xf_index = cell_format._get_xf_index()

        # Add row attributes where applicable.
        if spans:
            attributes.append(('spans', spans))

        if xf_index:
            attributes.append(('s', xf_index))

        if cell_format:
            attributes.append(('customFormat', 1))

        if height != self.original_row_height:
            attributes.append(('ht', height))

        if hidden:
            attributes.append(('hidden', 1))

        if height != self.original_row_height:
            attributes.append(('customHeight', 1))

        if level:
            attributes.append(('outlineLevel', level))

        if collapsed:
            attributes.append(('collapsed', 1))

        if self.excel_version == 2010:
            attributes.append(('x14ac:dyDescent', '0.25'))

        if empty_row:
            self._xml_empty_tag_unencoded('row', attributes)
        else:
            self._xml_start_tag_unencoded('row', attributes)

    def _write_empty_row(self, row, spans, properties=None):
        # Write and empty <row> element.
        self._write_row(row, spans, properties, empty_row=True)

    def _write_cell(self, row, col, cell):
        # Write the <cell> element.
        # Note. This is the innermost loop so efficiency is important.

        cell_range = xl_rowcol_to_cell_fast(row, col)

        attributes = [('r', cell_range)]

        if cell.format:
            # Add the cell format index.
            xf_index = cell.format._get_xf_index()
            attributes.append(('s', xf_index))
        elif row in self.set_rows and self.set_rows[row][1]:
            # Add the row format.
            row_xf = self.set_rows[row][1]
            attributes.append(('s', row_xf._get_xf_index()))
        elif col in self.col_formats:
            # Add the column format.
            col_xf = self.col_formats[col]
            attributes.append(('s', col_xf._get_xf_index()))

        type_cell_name = type(cell).__name__

        # Write the various cell types.
        if type_cell_name == 'Number':
            # Write a number.
            self._xml_number_element(cell.number, attributes)

        elif type_cell_name == 'String':
            # Write a string.
            string = cell.string

            if not self.constant_memory:
                # Write a shared string.
                self._xml_string_element(string, attributes)
            else:
                # Write an optimized in-line string.

                # Escape control characters. See SharedString.pm for details.
                string = re.sub('(_x[0-9a-fA-F]{4}_)', r'_x005F\1', string)
                string = re.sub(r'([\x00-\x08\x0B-\x1F])',
                                lambda match: "_x%04X_" %
                                ord(match.group(1)), string)

                # Escape non characters.
                if sys.version_info[0] == 2:
                    non_char1 = unichr(0xFFFE)
                    non_char2 = unichr(0xFFFF)
                else:
                    non_char1 = "\uFFFE"
                    non_char2 = "\uFFFF"

                string = re.sub(non_char1, '_xFFFE_', string)
                string = re.sub(non_char2, '_xFFFF_', string)

                # Write any rich strings without further tags.
                if re.search('^<r>', string) and re.search('</r>$', string):
                    self._xml_rich_inline_string(string, attributes)
                else:
                    # Add attribute to preserve leading or trailing whitespace.
                    preserve = 0
                    if re.search(r'^\s', string) or re.search(r'\s$', string):
                        preserve = 1

                    self._xml_inline_string(string, preserve, attributes)

        elif type_cell_name == 'Formula':
            # Write a formula. First check the formula value type.
            value = cell.value
            if type(cell.value) == bool:
                attributes.append(('t', 'b'))
                if cell.value:
                    value = 1
                else:
                    value = 0

            elif isinstance(cell.value, str_types):
                error_codes = ('#DIV/0!', '#N/A', '#NAME?', '#NULL!',
                               '#NUM!', '#REF!', '#VALUE!')
                if cell.value in error_codes:
                    attributes.append(('t', 'e'))
                else:
                    attributes.append(('t', 'str'))

            self._xml_formula_element(cell.formula, value, attributes)

        elif type_cell_name == 'ArrayFormula':
            # Write a array formula.

            # First check if the formula value is a string.
            try:
                float(cell.value)
            except ValueError:
                attributes.append(('t', 'str'))

            # Write an array formula.
            self._xml_start_tag('c', attributes)
            self._write_cell_array_formula(cell.formula, cell.range)
            self._write_cell_value(cell.value)
            self._xml_end_tag('c')

        elif type_cell_name == 'Blank':
            # Write a empty cell.
            self._xml_empty_tag('c', attributes)

        elif type_cell_name == 'Boolean':
            # Write a boolean cell.
            attributes.append(('t', 'b'))
            self._xml_start_tag('c', attributes)
            self._write_cell_value(cell.boolean)
            self._xml_end_tag('c')

    def _write_cell_value(self, value):
        # Write the cell value <v> element.
        if value is None:
            value = ''

        self._xml_data_element('v', value)

    def _write_cell_array_formula(self, formula, cell_range):
        # Write the cell array formula <f> element.
        attributes = [
            ('t', 'array'),
            ('ref', cell_range)
        ]

        self._xml_data_element('f', formula, attributes)

    def _write_sheet_pr(self):
        # Write the <sheetPr> element for Sheet level properties.
        attributes = []

        if (not self.fit_page
                and not self.filter_on
                and not self.tab_color
                and not self.outline_changed
                and not self.vba_codename):
            return

        if self.vba_codename:
            attributes.append(('codeName', self.vba_codename))

        if self.filter_on:
            attributes.append(('filterMode', 1))

        if (self.fit_page
                or self.tab_color
                or self.outline_changed):
            self._xml_start_tag('sheetPr', attributes)
            self._write_tab_color()
            self._write_outline_pr()
            self._write_page_set_up_pr()
            self._xml_end_tag('sheetPr')
        else:
            self._xml_empty_tag('sheetPr', attributes)

    def _write_page_set_up_pr(self):
        # Write the <pageSetUpPr> element.
        if not self.fit_page:
            return

        attributes = [('fitToPage', 1)]
        self._xml_empty_tag('pageSetUpPr', attributes)

    def _write_tab_color(self):
        # Write the <tabColor> element.
        color = self.tab_color

        if not color:
            return

        attributes = [('rgb', color)]

        self._xml_empty_tag('tabColor', attributes)

    def _write_outline_pr(self):
        # Write the <outlinePr> element.
        attributes = []

        if not self.outline_changed:
            return

        if self.outline_style:
            attributes.append(("applyStyles", 1))
        if not self.outline_below:
            attributes.append(("summaryBelow", 0))
        if not self.outline_right:
            attributes.append(("summaryRight", 0))
        if not self.outline_on:
            attributes.append(("showOutlineSymbols", 0))

        self._xml_empty_tag('outlinePr', attributes)

    def _write_row_breaks(self):
        # Write the <rowBreaks> element.
        page_breaks = self._sort_pagebreaks(self.hbreaks)

        if not page_breaks:
            return

        count = len(page_breaks)

        attributes = [
            ('count', count),
            ('manualBreakCount', count),
        ]

        self._xml_start_tag('rowBreaks', attributes)

        for row_num in page_breaks:
            self._write_brk(row_num, 16383)

        self._xml_end_tag('rowBreaks')

    def _write_col_breaks(self):
        # Write the <colBreaks> element.
        page_breaks = self._sort_pagebreaks(self.vbreaks)

        if not page_breaks:
            return

        count = len(page_breaks)

        attributes = [
            ('count', count),
            ('manualBreakCount', count),
        ]

        self._xml_start_tag('colBreaks', attributes)

        for col_num in page_breaks:
            self._write_brk(col_num, 1048575)

        self._xml_end_tag('colBreaks')

    def _write_brk(self, brk_id, brk_max):
        # Write the <brk> element.
        attributes = [
            ('id', brk_id),
            ('max', brk_max),
            ('man', 1)]

        self._xml_empty_tag('brk', attributes)

    def _write_merge_cells(self):
        # Write the <mergeCells> element.
        merged_cells = self.merge
        count = len(merged_cells)

        if not count:
            return

        attributes = [('count', count)]

        self._xml_start_tag('mergeCells', attributes)

        for merged_range in merged_cells:

            # Write the mergeCell element.
            self._write_merge_cell(merged_range)

        self._xml_end_tag('mergeCells')

    def _write_merge_cell(self, merged_range):
        # Write the <mergeCell> element.
        (row_min, col_min, row_max, col_max) = merged_range

        # Convert the merge dimensions to a cell range.
        cell_1 = xl_rowcol_to_cell(row_min, col_min)
        cell_2 = xl_rowcol_to_cell(row_max, col_max)
        ref = cell_1 + ':' + cell_2

        attributes = [('ref', ref)]

        self._xml_empty_tag('mergeCell', attributes)

    def _write_hyperlinks(self):
        # Process any stored hyperlinks in row/col order and write the
        # <hyperlinks> element. The attributes are different for internal
        # and external links.
        hlink_refs = []
        display = None

        # Sort the hyperlinks into row order.
        row_nums = sorted(self.hyperlinks.keys())

        # Exit if there are no hyperlinks to process.
        if not row_nums:
            return

        # Iterate over the rows.
        for row_num in row_nums:
            # Sort the hyperlinks into column order.
            col_nums = sorted(self.hyperlinks[row_num].keys())

            # Iterate over the columns.
            for col_num in col_nums:
                # Get the link data for this cell.
                link = self.hyperlinks[row_num][col_num]
                link_type = link["link_type"]

                # If the cell isn't a string then we have to add the url as
                # the string to display.
                if (self.table
                        and self.table[row_num]
                        and self.table[row_num][col_num]):
                    cell = self.table[row_num][col_num]
                    if type(cell).__name__ != 'String':
                        display = link["url"]

                if link_type == 1:
                    # External link with rel file relationship.
                    self.rel_count += 1

                    hlink_refs.append([link_type,
                                       row_num,
                                       col_num,
                                       self.rel_count,
                                       link["str"],
                                       display,
                                       link["tip"]])

                    # Links for use by the packager.
                    self.external_hyper_links.append(['/hyperlink',
                                                      link["url"], 'External'])
                else:
                    # Internal link with rel file relationship.
                    hlink_refs.append([link_type,
                                       row_num,
                                       col_num,
                                       link["url"],
                                       link["str"],
                                       link["tip"]])

        # Write the hyperlink elements.
        self._xml_start_tag('hyperlinks')

        for args in hlink_refs:
            link_type = args.pop(0)

            if link_type == 1:
                self._write_hyperlink_external(*args)
            elif link_type == 2:
                self._write_hyperlink_internal(*args)

        self._xml_end_tag('hyperlinks')

    def _write_hyperlink_external(self, row, col, id_num, location=None,
                                  display=None, tooltip=None):
        # Write the <hyperlink> element for external links.
        ref = xl_rowcol_to_cell(row, col)
        r_id = 'rId' + str(id_num)

        attributes = [
            ('ref', ref),
            ('r:id', r_id)]

        if location is not None:
            attributes.append(('location', location))
        if display is not None:
            attributes.append(('display', display))
        if tooltip is not None:
            attributes.append(('tooltip', tooltip))

        self._xml_empty_tag('hyperlink', attributes)

    def _write_hyperlink_internal(self, row, col, location=None, display=None,
                                  tooltip=None):
        # Write the <hyperlink> element for internal links.
        ref = xl_rowcol_to_cell(row, col)

        attributes = [
            ('ref', ref),
            ('location', location)]

        if tooltip is not None:
            attributes.append(('tooltip', tooltip))
        attributes.append(('display', display))

        self._xml_empty_tag('hyperlink', attributes)

    def _write_auto_filter(self):
        # Write the <autoFilter> element.
        if not self.autofilter_ref:
            return

        attributes = [('ref', self.autofilter_ref)]

        if self.filter_on:
            # Autofilter defined active filters.
            self._xml_start_tag('autoFilter', attributes)
            self._write_autofilters()
            self._xml_end_tag('autoFilter')

        else:
            # Autofilter defined without active filters.
            self._xml_empty_tag('autoFilter', attributes)

    def _write_autofilters(self):
        # Function to iterate through the columns that form part of an
        # autofilter range and write the appropriate filters.
        (col1, col2) = self.filter_range

        for col in range(col1, col2 + 1):
            # Skip if column doesn't have an active filter.
            if col not in self.filter_cols:
                continue

            # Retrieve the filter tokens and write the autofilter records.
            tokens = self.filter_cols[col]
            filter_type = self.filter_type[col]

            # Filters are relative to first column in the autofilter.
            self._write_filter_column(col - col1, filter_type, tokens)

    def _write_filter_column(self, col_id, filter_type, filters):
        # Write the <filterColumn> element.
        attributes = [('colId', col_id)]

        self._xml_start_tag('filterColumn', attributes)

        if filter_type == 1:
            # Type == 1 is the new XLSX style filter.
            self._write_filters(filters)
        else:
            # Type == 0 is the classic "custom" filter.
            self._write_custom_filters(filters)

        self._xml_end_tag('filterColumn')

    def _write_filters(self, filters):
        # Write the <filters> element.
        non_blanks = [filter for filter in filters
                      if str(filter).lower() != 'blanks']
        attributes = []

        if len(filters) != len(non_blanks):
            attributes = [('blank', 1)]

        if len(filters) == 1 and len(non_blanks) == 0:
            # Special case for blank cells only.
            self._xml_empty_tag('filters', attributes)
        else:
            # General case.
            self._xml_start_tag('filters', attributes)

            for autofilter in sorted(non_blanks):
                self._write_filter(autofilter)

            self._xml_end_tag('filters')

    def _write_filter(self, val):
        # Write the <filter> element.
        attributes = [('val', val)]

        self._xml_empty_tag('filter', attributes)

    def _write_custom_filters(self, tokens):
        # Write the <customFilters> element.
        if len(tokens) == 2:
            # One filter expression only.
            self._xml_start_tag('customFilters')
            self._write_custom_filter(*tokens)
            self._xml_end_tag('customFilters')
        else:
            # Two filter expressions.
            attributes = []

            # Check if the "join" operand is "and" or "or".
            if tokens[2] == 0:
                attributes = [('and', 1)]
            else:
                attributes = [('and', 0)]

            # Write the two custom filters.
            self._xml_start_tag('customFilters', attributes)
            self._write_custom_filter(tokens[0], tokens[1])
            self._write_custom_filter(tokens[3], tokens[4])
            self._xml_end_tag('customFilters')

    def _write_custom_filter(self, operator, val):
        # Write the <customFilter> element.
        attributes = []

        operators = {
            1: 'lessThan',
            2: 'equal',
            3: 'lessThanOrEqual',
            4: 'greaterThan',
            5: 'notEqual',
            6: 'greaterThanOrEqual',
            22: 'equal',
        }

        # Convert the operator from a number to a descriptive string.
        if operators[operator] is not None:
            operator = operators[operator]
        else:
            warn("Unknown operator = %s" % operator)

        # The 'equal' operator is the default attribute and isn't stored.
        if not operator == 'equal':
            attributes.append(('operator', operator))
        attributes.append(('val', val))

        self._xml_empty_tag('customFilter', attributes)

    def _write_sheet_protection(self):
        # Write the <sheetProtection> element.
        attributes = []

        if not self.protect_options:
            return

        options = self.protect_options

        if options['password']:
            attributes.append(('password', options['password']))
        if options['sheet']:
            attributes.append(('sheet', 1))
        if options['content']:
            attributes.append(('content', 1))
        if not options['objects']:
            attributes.append(('objects', 1))
        if not options['scenarios']:
            attributes.append(('scenarios', 1))
        if options['format_cells']:
            attributes.append(('formatCells', 0))
        if options['format_columns']:
            attributes.append(('formatColumns', 0))
        if options['format_rows']:
            attributes.append(('formatRows', 0))
        if options['insert_columns']:
            attributes.append(('insertColumns', 0))
        if options['insert_rows']:
            attributes.append(('insertRows', 0))
        if options['insert_hyperlinks']:
            attributes.append(('insertHyperlinks', 0))
        if options['delete_columns']:
            attributes.append(('deleteColumns', 0))
        if options['delete_rows']:
            attributes.append(('deleteRows', 0))
        if not options['select_locked_cells']:
            attributes.append(('selectLockedCells', 1))
        if options['sort']:
            attributes.append(('sort', 0))
        if options['autofilter']:
            attributes.append(('autoFilter', 0))
        if options['pivot_tables']:
            attributes.append(('pivotTables', 0))
        if not options['select_unlocked_cells']:
            attributes.append(('selectUnlockedCells', 1))

        self._xml_empty_tag('sheetProtection', attributes)

    def _write_drawings(self):
        # Write the <drawing> elements.
        if not self.drawing:
            return

        self.rel_count += 1
        self._write_drawing(self.rel_count)

    def _write_drawing(self, drawing_id):
        # Write the <drawing> element.
        r_id = 'rId' + str(drawing_id)

        attributes = [('r:id', r_id)]

        self._xml_empty_tag('drawing', attributes)

    def _write_legacy_drawing(self):
        # Write the <legacyDrawing> element.
        if not self.has_vml:
            return

        # Increment the relationship id for any drawings or comments.
        self.rel_count += 1
        r_id = 'rId' + str(self.rel_count)

        attributes = [('r:id', r_id)]

        self._xml_empty_tag('legacyDrawing', attributes)

    def _write_legacy_drawing_hf(self):
        # Write the <legacyDrawingHF> element.
        if not self.has_header_vml:
            return

        # Increment the relationship id for any drawings or comments.
        self.rel_count += 1
        r_id = 'rId' + str(self.rel_count)

        attributes = [('r:id', r_id)]

        self._xml_empty_tag('legacyDrawingHF', attributes)

    def _write_data_validations(self):
        # Write the <dataValidations> element.
        validations = self.validations
        count = len(validations)

        if not count:
            return

        attributes = [('count', count)]

        self._xml_start_tag('dataValidations', attributes)

        for validation in validations:

            # Write the dataValidation element.
            self._write_data_validation(validation)

        self._xml_end_tag('dataValidations')

    def _write_data_validation(self, options):
        # Write the <dataValidation> element.
        sqref = ''
        attributes = []

        # Set the cell range(s) for the data validation.
        for cells in options['cells']:

            # Add a space between multiple cell ranges.
            if sqref != '':
                sqref += ' '

            (row_first, col_first, row_last, col_last) = cells

            # Swap last row/col for first row/col as necessary
            if row_first > row_last:
                (row_first, row_last) = (row_last, row_first)

            if col_first > col_last:
                (col_first, col_last) = (col_last, col_first)

            # If the first and last cell are the same write a single cell.
            if (row_first == row_last) and (col_first == col_last):
                sqref += xl_rowcol_to_cell(row_first, col_first)
            else:
                sqref += xl_range(row_first, col_first, row_last, col_last)

        if options['validate'] != 'none':
            attributes.append(('type', options['validate']))

            if options['criteria'] != 'between':
                attributes.append(('operator', options['criteria']))

        if 'error_type' in options:
            if options['error_type'] == 1:
                attributes.append(('errorStyle', 'warning'))
            if options['error_type'] == 2:
                attributes.append(('errorStyle', 'information'))

        if options['ignore_blank']:
            attributes.append(('allowBlank', 1))

        if not options['dropdown']:
            attributes.append(('showDropDown', 1))

        if options['show_input']:
            attributes.append(('showInputMessage', 1))

        if options['show_error']:
            attributes.append(('showErrorMessage', 1))

        if 'error_title' in options:
            attributes.append(('errorTitle', options['error_title']))

        if 'error_message' in options:
            attributes.append(('error', options['error_message']))

        if 'input_title' in options:
            attributes.append(('promptTitle', options['input_title']))

        if 'input_message' in options:
            attributes.append(('prompt', options['input_message']))

        attributes.append(('sqref', sqref))

        if options['validate'] == 'none':
            self._xml_empty_tag('dataValidation', attributes)
        else:
            self._xml_start_tag('dataValidation', attributes)

            # Write the formula1 element.
            self._write_formula_1(options['value'])

            # Write the formula2 element.
            if options['maximum'] is not None:
                self._write_formula_2(options['maximum'])

            self._xml_end_tag('dataValidation')

    def _write_formula_1(self, formula):
        # Write the <formula1> element.

        if type(formula) is list:
            formula = self._csv_join(*formula)
            formula = '"%s"' % formula
        else:
            # Check if the formula is a number.
            try:
                float(formula)
            except ValueError:
                # Not a number. Remove the formula '=' sign if it exists.
                if formula.startswith('='):
                    formula = formula.lstrip('=')

        self._xml_data_element('formula1', formula)

    def _write_formula_2(self, formula):
        # Write the <formula2> element.

        # Check if the formula is a number.
        try:
            float(formula)
        except ValueError:
            # Not a number. Remove the formula '=' sign if it exists.
            if formula.startswith('='):
                formula = formula.lstrip('=')

        self._xml_data_element('formula2', formula)

    def _write_conditional_formats(self):
        # Write the Worksheet conditional formats.
        ranges = sorted(self.cond_formats.keys())

        if not ranges:
            return

        for cond_range in ranges:
            self._write_conditional_formatting(cond_range,
                                               self.cond_formats[cond_range])

    def _write_conditional_formatting(self, cond_range, params):
        # Write the <conditionalFormatting> element.
        attributes = [('sqref', cond_range)]
        self._xml_start_tag('conditionalFormatting', attributes)
        for param in params:
            # Write the cfRule element.
            self._write_cf_rule(param)
        self._xml_end_tag('conditionalFormatting')

    def _write_cf_rule(self, params):
        # Write the <cfRule> element.
        attributes = [('type', params['type'])]

        if 'format' in params and params['format'] is not None:
            attributes.append(('dxfId', params['format']))

        attributes.append(('priority', params['priority']))

        if params.get('stop_if_true'):
            attributes.append(('stopIfTrue', 1))

        if params['type'] == 'cellIs':
            attributes.append(('operator', params['criteria']))

            self._xml_start_tag('cfRule', attributes)

            if 'minimum' in params and 'maximum' in params:
                self._write_formula_element(params['minimum'])
                self._write_formula_element(params['maximum'])
            else:
                self._write_formula_element(params['value'])

            self._xml_end_tag('cfRule')

        elif params['type'] == 'aboveAverage':
            if re.search('below', params['criteria']):
                attributes.append(('aboveAverage', 0))

            if re.search('equal', params['criteria']):
                attributes.append(('equalAverage', 1))

            if re.search('[123] std dev', params['criteria']):
                match = re.search('([123]) std dev', params['criteria'])
                attributes.append(('stdDev', match.group(1)))

            self._xml_empty_tag('cfRule', attributes)

        elif params['type'] == 'top10':
            if 'criteria' in params and params['criteria'] == '%':
                attributes.append(('percent', 1))

            if 'direction' in params:
                attributes.append(('bottom', 1))

            rank = params['value'] or 10
            attributes.append(('rank', rank))

            self._xml_empty_tag('cfRule', attributes)

        elif params['type'] == 'duplicateValues':
            self._xml_empty_tag('cfRule', attributes)

        elif params['type'] == 'uniqueValues':
            self._xml_empty_tag('cfRule', attributes)

        elif (params['type'] == 'containsText'
              or params['type'] == 'notContainsText'
              or params['type'] == 'beginsWith'
              or params['type'] == 'endsWith'):
            attributes.append(('operator', params['criteria']))
            attributes.append(('text', params['value']))
            self._xml_start_tag('cfRule', attributes)
            self._write_formula_element(params['formula'])
            self._xml_end_tag('cfRule')

        elif params['type'] == 'timePeriod':
            attributes.append(('timePeriod', params['criteria']))
            self._xml_start_tag('cfRule', attributes)
            self._write_formula_element(params['formula'])
            self._xml_end_tag('cfRule')

        elif (params['type'] == 'containsBlanks'
              or params['type'] == 'notContainsBlanks'
              or params['type'] == 'containsErrors'
              or params['type'] == 'notContainsErrors'):
            self._xml_start_tag('cfRule', attributes)
            self._write_formula_element(params['formula'])
            self._xml_end_tag('cfRule')

        elif params['type'] == 'colorScale':
            self._xml_start_tag('cfRule', attributes)
            self._write_color_scale(params)
            self._xml_end_tag('cfRule')

        elif params['type'] == 'dataBar':
            self._xml_start_tag('cfRule', attributes)
            self._write_data_bar(params)

            if params.get('is_data_bar_2010'):
                self._write_data_bar_ext(params)

            self._xml_end_tag('cfRule')

        elif params['type'] == 'expression':
            self._xml_start_tag('cfRule', attributes)
            self._write_formula_element(params['criteria'])
            self._xml_end_tag('cfRule')

        elif params['type'] == 'iconSet':
            self._xml_start_tag('cfRule', attributes)
            self._write_icon_set(params)
            self._xml_end_tag('cfRule')

    def _write_formula_element(self, formula):
        # Write the <formula> element.

        # Check if the formula is a number.
        try:
            float(formula)
        except ValueError:
            # Not a number. Remove the formula '=' sign if it exists.
            if formula.startswith('='):
                formula = formula.lstrip('=')

        self._xml_data_element('formula', formula)

    def _write_color_scale(self, param):
        # Write the <colorScale> element.

        self._xml_start_tag('colorScale')

        self._write_cfvo(param['min_type'], param['min_value'])

        if param['mid_type'] is not None:
            self._write_cfvo(param['mid_type'], param['mid_value'])

        self._write_cfvo(param['max_type'], param['max_value'])

        self._write_color('rgb', param['min_color'])

        if param['mid_color'] is not None:
            self._write_color('rgb', param['mid_color'])

        self._write_color('rgb', param['max_color'])

        self._xml_end_tag('colorScale')

    def _write_data_bar(self, param):
        # Write the <dataBar> element.
        attributes = []

        # Min and max bar lengths in in the spec but not supported directly by
        # Excel.
        if param.get('min_length'):
            attributes.append(('minLength', param['min_length']))

        if param.get('max_length'):
            attributes.append(('maxLength', param['max_length']))

        if param.get('bar_only'):
            attributes.append(('showValue', 0))

        self._xml_start_tag('dataBar', attributes)

        self._write_cfvo(param['min_type'], param['min_value'])
        self._write_cfvo(param['max_type'], param['max_value'])
        self._write_color('rgb', param['bar_color'])

        self._xml_end_tag('dataBar')

    def _write_data_bar_ext(self, param):
        # Write the <extLst> dataBar extension element.

        # Create a pseudo GUID for each unique Excel 2010 data bar.
        worksheet_count = self.index + 1
        data_bar_count = len(self.data_bars_2010) + 1
        guid = "{DA7ABA51-AAAA-BBBB-%04X-%012X}" % (worksheet_count,
                                                    data_bar_count)

        # Store the 2010 data bar parameters to write the extLst elements.
        param['guid'] = guid
        self.data_bars_2010.append(param)

        self._xml_start_tag('extLst')
        self._write_ext('{B025F937-C7B1-47D3-B67F-A62EFF666E3E}')
        self._xml_data_element('x14:id', guid)
        self._xml_end_tag('ext')
        self._xml_end_tag('extLst')

    def _write_icon_set(self, param):
        # Write the <iconSet> element.
        attributes = []

        # Don't set attribute for default style.
        if param['icon_style'] != '3TrafficLights':
            attributes = [('iconSet', param['icon_style'])]

        if param.get('icons_only'):
            attributes.append(('showValue', 0))

        if param.get('reverse_icons'):
            attributes.append(('reverse', 1))

        self._xml_start_tag('iconSet', attributes)

        # Write the properties for different icon styles.
        for icon in reversed(param['icons']):
            self._write_cfvo(
                icon['type'],
                icon['value'],
                icon['criteria'])

        self._xml_end_tag('iconSet')

    def _write_cfvo(self, cf_type, val, criteria=None):
        # Write the <cfvo> element.
        attributes = [('type', cf_type)]

        if val is not None:
            attributes.append(('val', val))

        if criteria:
            attributes.append(('gte', 0))

        self._xml_empty_tag('cfvo', attributes)

    def _write_color(self, name, value):
        # Write the <color> element.
        attributes = [(name, value)]

        self._xml_empty_tag('color', attributes)

    def _write_selections(self):
        # Write the <selection> elements.
        for selection in self.selections:
            self._write_selection(*selection)

    def _write_selection(self, pane, active_cell, sqref):
        # Write the <selection> element.
        attributes = []

        if pane:
            attributes.append(('pane', pane))

        if active_cell:
            attributes.append(('activeCell', active_cell))

        if sqref:
            attributes.append(('sqref', sqref))

        self._xml_empty_tag('selection', attributes)

    def _write_panes(self):
        # Write the frozen or split <pane> elements.
        panes = self.panes

        if not len(panes):
            return

        if panes[4] == 2:
            self._write_split_panes(*panes)
        else:
            self._write_freeze_panes(*panes)

    def _write_freeze_panes(self, row, col, top_row, left_col, pane_type):
        # Write the <pane> element for freeze panes.
        attributes = []

        y_split = row
        x_split = col
        top_left_cell = xl_rowcol_to_cell(top_row, left_col)
        active_pane = ''
        state = ''
        active_cell = ''
        sqref = ''

        # Move user cell selection to the panes.
        if self.selections:
            (_, active_cell, sqref) = self.selections[0]
            self.selections = []

        # Set the active pane.
        if row and col:
            active_pane = 'bottomRight'

            row_cell = xl_rowcol_to_cell(row, 0)
            col_cell = xl_rowcol_to_cell(0, col)

            self.selections.append(['topRight', col_cell, col_cell])
            self.selections.append(['bottomLeft', row_cell, row_cell])
            self.selections.append(['bottomRight', active_cell, sqref])

        elif col:
            active_pane = 'topRight'
            self.selections.append(['topRight', active_cell, sqref])

        else:
            active_pane = 'bottomLeft'
            self.selections.append(['bottomLeft', active_cell, sqref])

        # Set the pane type.
        if pane_type == 0:
            state = 'frozen'
        elif pane_type == 1:
            state = 'frozenSplit'
        else:
            state = 'split'

        if x_split:
            attributes.append(('xSplit', x_split))

        if y_split:
            attributes.append(('ySplit', y_split))

        attributes.append(('topLeftCell', top_left_cell))
        attributes.append(('activePane', active_pane))
        attributes.append(('state', state))

        self._xml_empty_tag('pane', attributes)

    def _write_split_panes(self, row, col, top_row, left_col, pane_type):
        # Write the <pane> element for split panes.
        attributes = []
        has_selection = 0
        active_pane = ''
        active_cell = ''
        sqref = ''

        y_split = row
        x_split = col

        # Move user cell selection to the panes.
        if self.selections:
            (_, active_cell, sqref) = self.selections[0]
            self.selections = []
            has_selection = 1

        # Convert the row and col to 1/20 twip units with padding.
        if y_split:
            y_split = int(20 * y_split + 300)

        if x_split:
            x_split = self._calculate_x_split_width(x_split)

        # For non-explicit topLeft definitions, estimate the cell offset based
        # on the pixels dimensions. This is only a workaround and doesn't take
        # adjusted cell dimensions into account.
        if top_row == row and left_col == col:
            top_row = int(0.5 + (y_split - 300) / 20 / 15)
            left_col = int(0.5 + (x_split - 390) / 20 / 3 * 4 / 64)

        top_left_cell = xl_rowcol_to_cell(top_row, left_col)

        # If there is no selection set the active cell to the top left cell.
        if not has_selection:
            active_cell = top_left_cell
            sqref = top_left_cell

        # Set the Cell selections.
        if row and col:
            active_pane = 'bottomRight'

            row_cell = xl_rowcol_to_cell(top_row, 0)
            col_cell = xl_rowcol_to_cell(0, left_col)

            self.selections.append(['topRight', col_cell, col_cell])
            self.selections.append(['bottomLeft', row_cell, row_cell])
            self.selections.append(['bottomRight', active_cell, sqref])

        elif col:
            active_pane = 'topRight'
            self.selections.append(['topRight', active_cell, sqref])

        else:
            active_pane = 'bottomLeft'
            self.selections.append(['bottomLeft', active_cell, sqref])

        # Format splits to the same precision as Excel.
        if x_split:
            attributes.append(('xSplit', "%.16g" % x_split))

        if y_split:
            attributes.append(('ySplit', "%.16g" % y_split))

        attributes.append(('topLeftCell', top_left_cell))

        if has_selection:
            attributes.append(('activePane', active_pane))

        self._xml_empty_tag('pane', attributes)

    def _calculate_x_split_width(self, width):
        # Convert column width from user units to pane split width.

        max_digit_width = 7  # For Calabri 11.
        padding = 5

        # Convert to pixels.
        if width < 1:
            pixels = int(width * (max_digit_width + padding) + 0.5)
        else:
            pixels = int(width * max_digit_width + 0.5) + padding

        # Convert to points.
        points = pixels * 3 / 4

        # Convert to twips (twentieths of a point).
        twips = points * 20

        # Add offset/padding.
        width = twips + 390

        return width

    def _write_table_parts(self):
        # Write the <tableParts> element.
        tables = self.tables
        count = len(tables)

        # Return if worksheet doesn't contain any tables.
        if not count:
            return

        attributes = [('count', count,)]

        self._xml_start_tag('tableParts', attributes)

        for _ in tables:

            # Write the tablePart element.
            self.rel_count += 1
            self._write_table_part(self.rel_count)

        self._xml_end_tag('tableParts')

    def _write_table_part(self, r_id):
        # Write the <tablePart> element.

        r_id = 'rId' + str(r_id)

        attributes = [('r:id', r_id,)]

        self._xml_empty_tag('tablePart', attributes)

    def _write_ext_list(self):
        # Write the <extLst> element for data bars and sparklines.
        has_data_bars = len(self.data_bars_2010)
        has_sparklines = len(self.sparklines)

        if not has_data_bars and not has_sparklines:
            return

        # Write the extLst element.
        self._xml_start_tag('extLst')

        if has_data_bars:
            self._write_ext_list_data_bars()

        if has_sparklines:
            self._write_ext_list_sparklines()

        self._xml_end_tag('extLst')

    def _write_ext_list_data_bars(self):
        # Write the Excel 2010 data_bar subelements.
        self._write_ext('{78C0D931-6437-407d-A8EE-F0AAD7539E65}')

        self._xml_start_tag('x14:conditionalFormattings')

        # Write the Excel 2010 conditional formatting data bar elements.
        for data_bar in self.data_bars_2010:
            # Write the x14:conditionalFormatting element.
            self._write_conditional_formatting_2010(data_bar)

        self._xml_end_tag('x14:conditionalFormattings')
        self._xml_end_tag('ext')

    def _write_conditional_formatting_2010(self, data_bar):
        # Write the <x14:conditionalFormatting> element.
        xmlns_xm = 'http://schemas.microsoft.com/office/excel/2006/main'

        attributes = [('xmlns:xm', xmlns_xm)]

        self._xml_start_tag('x14:conditionalFormatting', attributes)

        # Write the x14:cfRule element.
        self._write_x14_cf_rule(data_bar)

        # Write the x14:dataBar element.
        self._write_x14_data_bar(data_bar)

        # Write the x14 max and min data bars.
        self._write_x14_cfvo(data_bar['x14_min_type'], data_bar['min_value'])
        self._write_x14_cfvo(data_bar['x14_max_type'], data_bar['max_value'])

        if not data_bar['bar_no_border']:
            # Write the x14:borderColor element.
            self._write_x14_border_color(data_bar['bar_border_color'])

        # Write the x14:negativeFillColor element.
        if not data_bar['bar_negative_color_same']:
            self._write_x14_negative_fill_color(
                data_bar['bar_negative_color'])

        # Write the x14:negativeBorderColor element.
        if (not data_bar['bar_no_border'] and
                not data_bar['bar_negative_border_color_same']):
            self._write_x14_negative_border_color(
                data_bar['bar_negative_border_color'])

        # Write the x14:axisColor element.
        if data_bar['bar_axis_position'] is not 'none':
            self._write_x14_axis_color(data_bar['bar_axis_color'])

        self._xml_end_tag('x14:dataBar')
        self._xml_end_tag('x14:cfRule')

        # Write the xm:sqref element.
        self._xml_data_element('xm:sqref', data_bar['range'])

        self._xml_end_tag('x14:conditionalFormatting')

    def _write_x14_cf_rule(self, data_bar):
        # Write the <x14:cfRule> element.
        rule_type = 'dataBar'
        guid = data_bar['guid']
        attributes = [('type', rule_type), ('id', guid)]

        self._xml_start_tag('x14:cfRule', attributes)

    def _write_x14_data_bar(self, data_bar):
        # Write the <x14:dataBar> element.
        min_length = 0
        max_length = 100

        attributes = [
            ('minLength', min_length),
            ('maxLength', max_length),
        ]

        if not data_bar['bar_no_border']:
            attributes.append(('border', 1))

        if data_bar['bar_solid']:
            attributes.append(('gradient', 0))

        if data_bar['bar_direction'] is 'left':
            attributes.append(('direction', 'leftToRight'))

        if data_bar['bar_direction'] is 'right':
            attributes.append(('direction', 'rightToLeft'))

        if data_bar['bar_negative_color_same']:
            attributes.append(('negativeBarColorSameAsPositive', 1))

        if (not data_bar['bar_no_border'] and
                not data_bar['bar_negative_border_color_same']):
            attributes.append(('negativeBarBorderColorSameAsPositive', 0))

        if data_bar['bar_axis_position'] is 'middle':
            attributes.append(('axisPosition', 'middle'))

        if data_bar['bar_axis_position'] is 'none':
            attributes.append(('axisPosition', 'none'))

        self._xml_start_tag('x14:dataBar', attributes)

    def _write_x14_cfvo(self, rule_type, value):
        # Write the <x14:cfvo> element.
        attributes = [('type', rule_type)]

        if rule_type in ('min', 'max', 'autoMin', 'autoMax'):
            self._xml_empty_tag('x14:cfvo', attributes)
        else:
            self._xml_start_tag('x14:cfvo', attributes)
            self._xml_data_element('xm:f', value)
            self._xml_end_tag('x14:cfvo')

    def _write_x14_border_color(self, rgb):
        # Write the <x14:borderColor> element.
        attributes = [('rgb', rgb)]
        self._xml_empty_tag('x14:borderColor', attributes)

    def _write_x14_negative_fill_color(self, rgb):
        # Write the <x14:negativeFillColor> element.
        attributes = [('rgb', rgb)]
        self._xml_empty_tag('x14:negativeFillColor', attributes)

    def _write_x14_negative_border_color(self, rgb):
        # Write the <x14:negativeBorderColor> element.
        attributes = [('rgb', rgb)]
        self._xml_empty_tag('x14:negativeBorderColor', attributes)

    def _write_x14_axis_color(self, rgb):
        # Write the <x14:axisColor> element.
        attributes = [('rgb', rgb)]
        self._xml_empty_tag('x14:axisColor', attributes)

    def _write_ext_list_sparklines(self):
        # Write the sparkline extension sub-elements.
        self._write_ext('{05C60535-1F16-4fd2-B633-F4F36F0B64E0}')

        # Write the x14:sparklineGroups element.
        self._write_sparkline_groups()

        # Write the sparkline elements.
        for sparkline in reversed(self.sparklines):

            # Write the x14:sparklineGroup element.
            self._write_sparkline_group(sparkline)

            # Write the x14:colorSeries element.
            self._write_color_series(sparkline['series_color'])

            # Write the x14:colorNegative element.
            self._write_color_negative(sparkline['negative_color'])

            # Write the x14:colorAxis element.
            self._write_color_axis()

            # Write the x14:colorMarkers element.
            self._write_color_markers(sparkline['markers_color'])

            # Write the x14:colorFirst element.
            self._write_color_first(sparkline['first_color'])

            # Write the x14:colorLast element.
            self._write_color_last(sparkline['last_color'])

            # Write the x14:colorHigh element.
            self._write_color_high(sparkline['high_color'])

            # Write the x14:colorLow element.
            self._write_color_low(sparkline['low_color'])

            if sparkline['date_axis']:
                self._xml_data_element('xm:f', sparkline['date_axis'])

            self._write_sparklines(sparkline)

            self._xml_end_tag('x14:sparklineGroup')

        self._xml_end_tag('x14:sparklineGroups')
        self._xml_end_tag('ext')

    def _write_sparklines(self, sparkline):
        # Write the <x14:sparklines> element and <x14:sparkline> sub-elements.

        # Write the sparkline elements.
        self._xml_start_tag('x14:sparklines')

        for i in range(sparkline['count']):
            spark_range = sparkline['ranges'][i]
            location = sparkline['locations'][i]

            self._xml_start_tag('x14:sparkline')
            self._xml_data_element('xm:f', spark_range)
            self._xml_data_element('xm:sqref', location)
            self._xml_end_tag('x14:sparkline')

        self._xml_end_tag('x14:sparklines')

    def _write_ext(self, uri):
        # Write the <ext> element.
        schema = 'http://schemas.microsoft.com/office/'
        xmlns_x14 = schema + 'spreadsheetml/2009/9/main'

        attributes = [
            ('xmlns:x14', xmlns_x14),
            ('uri', uri),
        ]

        self._xml_start_tag('ext', attributes)

    def _write_sparkline_groups(self):
        # Write the <x14:sparklineGroups> element.
        xmlns_xm = 'http://schemas.microsoft.com/office/excel/2006/main'

        attributes = [('xmlns:xm', xmlns_xm)]

        self._xml_start_tag('x14:sparklineGroups', attributes)

    def _write_sparkline_group(self, options):
        # Write the <x14:sparklineGroup> element.
        #
        # Example for order.
        #
        # <x14:sparklineGroup
        #     manualMax="0"
        #     manualMin="0"
        #     lineWeight="2.25"
        #     type="column"
        #     dateAxis="1"
        #     displayEmptyCellsAs="span"
        #     markers="1"
        #     high="1"
        #     low="1"
        #     first="1"
        #     last="1"
        #     negative="1"
        #     displayXAxis="1"
        #     displayHidden="1"
        #     minAxisType="custom"
        #     maxAxisType="custom"
        #     rightToLeft="1">
        #
        empty = options.get('empty')
        attributes = []

        if options.get('max') is not None:
            if options['max'] == 'group':
                options['cust_max'] = 'group'
            else:
                attributes.append(('manualMax', options['max']))
                options['cust_max'] = 'custom'

        if options.get('min') is not None:

            if options['min'] == 'group':
                options['cust_min'] = 'group'
            else:
                attributes.append(('manualMin', options['min']))
                options['cust_min'] = 'custom'

        # Ignore the default type attribute (line).
        if options['type'] != 'line':
            attributes.append(('type', options['type']))

        if options.get('weight'):
            attributes.append(('lineWeight', options['weight']))

        if options.get('date_axis'):
            attributes.append(('dateAxis', 1))

        if empty:
            attributes.append(('displayEmptyCellsAs', empty))

        if options.get('markers'):
            attributes.append(('markers', 1))

        if options.get('high'):
            attributes.append(('high', 1))

        if options.get('low'):
            attributes.append(('low', 1))

        if options.get('first'):
            attributes.append(('first', 1))

        if options.get('last'):
            attributes.append(('last', 1))

        if options.get('negative'):
            attributes.append(('negative', 1))

        if options.get('axis'):
            attributes.append(('displayXAxis', 1))

        if options.get('hidden'):
            attributes.append(('displayHidden', 1))

        if options.get('cust_min'):
            attributes.append(('minAxisType', options['cust_min']))

        if options.get('cust_max'):
            attributes.append(('maxAxisType', options['cust_max']))

        if options.get('reverse'):
            attributes.append(('rightToLeft', 1))

        self._xml_start_tag('x14:sparklineGroup', attributes)

    def _write_spark_color(self, element, color):
        # Helper function for the sparkline color functions below.
        attributes = []

        if color.get('rgb'):
            attributes.append(('rgb', color['rgb']))

        if color.get('theme'):
            attributes.append(('theme', color['theme']))

        if color.get('tint'):
            attributes.append(('tint', color['tint']))

        self._xml_empty_tag(element, attributes)

    def _write_color_series(self, color):
        # Write the <x14:colorSeries> element.
        self._write_spark_color('x14:colorSeries', color)

    def _write_color_negative(self, color):
        # Write the <x14:colorNegative> element.
        self._write_spark_color('x14:colorNegative', color)

    def _write_color_axis(self):
        # Write the <x14:colorAxis> element.
        self._write_spark_color('x14:colorAxis', {'rgb': 'FF000000'})

    def _write_color_markers(self, color):
        # Write the <x14:colorMarkers> element.
        self._write_spark_color('x14:colorMarkers', color)

    def _write_color_first(self, color):
        # Write the <x14:colorFirst> element.
        self._write_spark_color('x14:colorFirst', color)

    def _write_color_last(self, color):
        # Write the <x14:colorLast> element.
        self._write_spark_color('x14:colorLast', color)

    def _write_color_high(self, color):
        # Write the <x14:colorHigh> element.
        self._write_spark_color('x14:colorHigh', color)

    def _write_color_low(self, color):
        # Write the <x14:colorLow> element.
        self._write_spark_color('x14:colorLow', color)

    def _write_phonetic_pr(self):
        # Write the <phoneticPr> element.
        attributes = [
            ('fontId', '0'),
            ('type', 'noConversion'),
        ]

        self._xml_empty_tag('phoneticPr', attributes)
